Temporal Table:

Temporal table is a table that records the period of time when a row is valid.

A period is an interval of time that is defined by two date time columns in a temporal table. A period
contains a begin column and an end column. The begin column indicates the beginning of the period,
and the end column indicates the end of the period. The beginning value of a period is inclusive, but the
ending value of a period is exclusive. For example, if the begin column has a value of '01/01/1995', that
date belongs in the row. Whereas, if the end column has a value of '03/21/1995', that date is not part of
the row.

DB2® supports two types of periods, which are the system period (SYSTEM_TIME) and the application
period (BUSINESS_TIME). The system period consists of a pair of columns with system-maintained values
that indicate the period of time when a row is valid. The begin column contains the timestamp value for
when a row is created. The end column contains the timestamp value for when a row is updated or
deleted.

The system period is meaningful because of system-period data versioning. System-period data
versioning specifies that old rows are archived into another table. The table that contains the current
active rows of a table is called the system-period temporal table. The table that contains the archived
rows is called the history table. You can delete the rows from the history table when those rows are no
longer needed, if you have the correct authorization.

When you define a base table to use system-period data versioning, or when you define system-period
data versioning on an existing table, you must create a history table, specify a name for the history
table, and create a table space to hold that table. You define versioning by issuing the ALTER TABLE ADD
VERSIONING statement with the USE HISTORY TABLE clause.

The application period consists of a pair of columns with application-maintained values that indicate the
period of time when a row is valid. The begin column contains the value for when a row is valid from.
The end column contains the value for when a row stops being valid. A table with only an application
period is called an application-period temporal table. When you use the application period, determine
the need for DB2 to enforce uniqueness across time. You can create a UNIQUE index that is unique over
a period of time.

A bitemporal table is a table that is both a system-period temporal table and an application-period
temporal table. You can use a bitemporal table to keep application period information and system-
based historical information. Therefore, you have a lot of flexibility in how you query data based on
periods of time.

T-SQL (Transact SQL):

Structured Query Language is a standardized computer language that was originally developed by IBM
for querying, altering and defining relational databases, using declarative statements. T-SQL expands on
the SQL standard to include procedural programming, local variables, various support functions for
 string processing, data processing, mathematics, etc. and changes to the DELETE and UPDATE
statements

 Keywords for flow control in Transact-SQL include BEGIN and END, BREAK, CONTINUE, GOTO, IF
and ELSE, RETURN, WAITFOR, and WHILE.
 Example 1:
IF DATEPART (dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1
PRINT 'It is the weekend.'
ELSE
PRINT 'It is a weekday.'

 Example 2:
DECLARE @i INT
SET @i = 0

WHILE @i < 5
BEGIN
PRINT 'Hello world.'
SET @i = @i + 1
END

Stored Procedure:

If a repetitive T-SQL task has to be executed within an application, then the best repository for it is a
program called a stored procedure, stored in SQL Server. Storing the code inside the SQL Server object
gives us many advantages, like:

 Security due to encryption

 Performance gains due to compilation
 Being able to hold the code in a central repository:
o Altering the code in SQL Server without replicating in several different programs
o Being able to keep statistics on the code to keep it optimized
 Reduction in the amount of data passed over a network by keeping the code on the server
 Hiding the raw data by allowing only stored procedures to gain access to the data

You may have executed some ad-hoc queries for tasks like inserting data, querying information in other
systems, or creating new database objects such as tables. All these tasks can be placed within a stored
procedure, so that any developer can run the same code without having to recreate the T-SQL
commands. Also, generalizing the code for all values makes it generic and reusable.

Stored procedures are more than just tools for performing repetitive tasks. There are two main types of
stored procedure – system stored procedures and user-defined stored procedures. We also have
extended stored procedures that can reside as either system or user-defined types. Extended stored
procedures give functionality that is not necessarily contained within SQL Server, like allowing DOS
commands to run and working with e-mail. It is also possible to create your own extended stored
procedures.

Sample example of stored procedure:

 /*
DECLARE @OutPutValue VARCHAR(100)
EXEC spExample 'CodeProject', @OutPutValue OUTPUT
PRINT @OutPutValue
*/
CREATE PROCEDURE [dbo].[spExample]
@parameter1 VARCHAR(100)
,@parameter2 VARCHAR(200) OUTPUT
AS
BEGIN
DECLARE @parameter3 VARCHAR(100)
SET @parameter3 = ' Your development resources.'
IF @parameter1 IS NOT NULL AND LEN(@parameter1) > 1
SELECT @parameter2 = 'The '
+ @parameter1
+ @parameter3
ELSE SELECT @parameter2 = 'CodeProject is cool!'
RETURN

END
GO

More details on Integration of an OLE Object with SQL Server will be found at this link.

2.1 System Stored Procedures

In SQL Server, many administrative and informational activities can be performed by using system
stored procedures. Every time we add or modify a table, make a backup plan, or perform any other
administrative function from within Enterprise Manager, we actually call a stored procedure specifically
written to complete the desired action. These stored procedures are known as system stored
procedures, which are functions that enhance the basic functionality of SQL Server itself, either by
extending the functionality of an existing system stored procedure or by creating new functionality that
enhances the basics of what is already there.

System stored procedures are prefixed by sp_, so it is not advisable to use sp_ for any of the stored
procedures that we create, unless they form a part of our SQL Server installation. Creating a stored
procedure prefixed with sp_ and placing it in the master database will make it available to any database
without the need to prefix the stored procedure with the name of the database. More details can be
found at this link.

Let's clarify this with an example. If we take the sp_who stored procedure, call it sp_mywho, store it in
the master database, and move to another database such as northwind, we can still execute sp_mywho,
rather than having to specify the procedure in the fully qualified manner as master.dbo.sp_mywho.

2.2 User Stored Procedures

A user stored procedure is any program that is stored and compiled within SQL Server (but not in the
master database) and prefixed with sp_. User stored procedures can be categorized into three distinct
types:

 User stored procedures

 Triggers, and
 User defined functions

Creation of stored procedure:

CREATE PROCEDURE ut_MidPoint @LowerNumber int, @HigherNumber int
AS
BEGIN

DECLARE @Mid int

IF @LowerNumber > @HigherNumber
RAISERROR('You have entered your numbers the wrong way round',16,1)

SET @Mid = ((@HigherNumber - @LowerNumber) / 2) + @LowerNumber

SELECT @Mid

END

Calling a Stored Procedure

Many people tend to miss a performance enhancement related to executing or calling a stored
procedure. For example, if you wanted to call the [Ten Most Expensive Products] stored procedure in
the northwind database, you can simply do it as:

Collapse | Copy Code

[Ten Most Expensive Products]

You can skip EXEC(UTE), but you will need it if you run one stored procedure within another.

However, this is not the most efficient way to call a stored procedure. When such a command is
processed, SQL Server has a hierarchical method of finding a stored procedure and executing it. First of
all, it will take the login ID of the user who wants to execute the procedure and see if a stored procedure
of that name exists. If it doesn't, then SQL Server will look for the stored procedure under the DBO login.
Hence, always fully qualify your stored procedure with the owner.

Schema
A database schema of a database system is its structure described in a formal language supported by the
database management system (DBMS) and refers to the organization of data as a blueprint of how a
database is constructed (divided into database tables in case of Relational Databases).

 Database schema is a way to logically group objects such as tables, views, stored procedures etc.
Think of a schema as a container of objects.
 You can assign user login permissions to a single schema so that the user can only access the objects
they are authorized to access.

Schemas can be created and altered in a database, and users can be granted access to a schema. A
schema can be owned by any user, and schema ownership is transferable.

Schema:Database:table::Floor plan:House:room

Trigger:

The SQL CREATE TRIGGER statement provides a way for the database management system to actively
control, monitor, and manage a group of tables whenever an insert, update, or delete operation is
performed. The statements specified in the SQL trigger are executed each time an SQL insert, update, or
delete operation is performed. An SQL trigger may call stored procedures or user-defined functions to
perform additional processing when the trigger is executed.

Unlike stored procedures, an SQL trigger cannot be directly called from an application. Instead, an SQL
trigger is invoked by the database management system on the execution of a triggering insert, update,
or delete operation. The definition of the SQL trigger is stored in the database management system and
is invoked by the database management system, when the SQL table, that the trigger is defined on, is
modified.

An SQL trigger can be created by specifying the CREATE TRIGGER SQL statement. The statements in the
routine-body of the SQL trigger are transformed by SQL into a program (*PGM) object. The program is
created in the schema specified by the trigger name qualifier. The specified trigger is registered in the
SYSTRIGGERS, SYSTRIGDEP, SYSTRIGCOL, and SYSTRIGUPD SQL Catalogs.

What is a Trigger
A trigger is a special kind of a store procedure that executes in response to certain action on the table
like insertion, deletion or updation of data. It is a database object which is bound to a table and is
executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the
required action no the table that they are assigned to.

Types Of Triggers
There are three action query types that you use in SQL which are INSERT, UPDATE and DELETE. So, there
are three types of triggers and hybrids that come from mixing and matching the events and timings that
fire them.

Basically, triggers are classified into two main types:-

(i) After Triggers (For Triggers)

(ii) Instead Of Triggers
(i) After Triggers
These triggers run after an insert, update or delete on a table. They are not supported for views.
AFTER TRIGGERS can be classified further into three types as:

(a) AFTER INSERT Trigger.

(b) AFTER UPDATE Trigger.
(c) AFTER DELETE Trigger.

Let’s create After triggers. First of all, let’s create a table and insert some sample data. Then, on this
table, I will be attaching several triggers.

CREATE TABLE Employee_Test

(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee_Test VALUES ('Anees',1000);

INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);

I will be creating an AFTER INSERT TRIGGER which will insert the rows inserted into the table into
another audit table. The main purpose of this audit table is to record the changes in the main table.
This can be thought of as a generic audit trigger.

Now, create the audit table as:-

Collapse | Copy Code

CREATE TABLE Employee_Test_Audit

(
Emp_ID int,
Emp_name varchar(100),
Emp_Sal decimal (10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)

(a) AFTRE INSERT Trigger

This trigger is fired after an INSERT on the table. Let’s create the trigger as:-

Collapse | Copy Code

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test]

FOR INSERT
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);

select @empid=i.Emp_ID from inserted i;

select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.';

insert into Employee_Test_Audit

(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER INSERT trigger fired.'

GO
The CREATE TRIGGER statement is used to create the trigger. THE ON clause specifies the table name
on which the trigger is to be attached. The FOR INSERT specifies that this is an AFTER INSERT trigger.
In place of FOR INSERT, AFTER INSERT can be used. Both of them mean the same.
In the trigger body, table named inserted has been used. This table is a logical table and contains
the row that has been inserted. I have selected the fields from the logical inserted table from the row
that has been inserted into different variables, and finally inserted those values into the Audit table.
To see the newly created trigger in action, lets insert a row into the main table as :

insert into Employee_Test values('Chris',1500);

Now, a record has been inserted into the Employee_Test table. The AFTER INSERT trigger attached to
this table has inserted the record into the Employee_Test_Audit as:-

6 Chris 1500.00 Inserted Record -- After Insert Trigger. 2008-04-26

12:00:55.700

(b) AFTER UPDATE Trigger

This trigger is fired after an update on the table. Let’s create the trigger as:-

CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test]

FOR UPDATE
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);

select @empid=i.Emp_ID from inserted i;

select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;

if update(Emp_Name)
set @audit_action='Updated Record -- After Update Trigger.';
if update(Emp_Sal)
set @audit_action='Updated Record -- After Update Trigger.';
 insert into
Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER UPDATE Trigger fired.'

GO
The AFTER UPDATE Trigger is created in which the updated record is inserted into the audit table.
There is no logical table updated like the logical table inserted. We can obtain the updated value
of a field from the update(column_name) function. In our trigger, we have used, if
update(Emp_Name) to check if the column Emp_Name has been updated. We have similarly
checked the column Emp_Sal for an update.
Let’s update a record column and see what happens.

update Employee_Test set Emp_Sal=1550 where Emp_ID=6

This inserts the row into the audit table as:-

6 Chris 1550.00 Updated Record -- After Update Trigger. 2008-04-26

12:38:11.843

(c) AFTER DELETE Trigger

This trigger is fired after a delete on the table. Let’s create the trigger as:-

CREATE TRIGGER trgAfterDelete ON [dbo].[Employee_Test]

AFTER DELETE
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);

select @empid=d.Emp_ID from deleted d;

select @empname=d.Emp_Name from deleted d;
select @empsal=d.Emp_Sal from deleted d;
set @audit_action='Deleted -- After Delete Trigger.';

insert into Employee_Test_Audit

(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER DELETE TRIGGER fired.'

GO
In this trigger, the deleted record’s data is picked from the logical deleted table and inserted into
the audit table.
Let’s fire a delete on the main table.
A record has been inserted into the audit table as:-

6 Chris 1550.00 Deleted -- After Delete Trigger. 2008-04-26 12:52:13.867

All the triggers can be enabled/disabled on the table using the statement

ALTER TABLE Employee_Test {ENABLE|DISBALE} TRIGGER ALL

Specific Triggers can be enabled or disabled as :-

ALTER TABLE Employee_Test DISABLE TRIGGER trgAfterDelete

This disables the After Delete Trigger named trgAfterDelete on the specified table.

(ii) Instead Of Triggers

These can be used as an interceptor for anything that anyonr tried to do on our table or view. If you
define an Instead Of trigger on a table for the Delete operation, they try to delete rows, and they will
not actually get deleted (unless you issue another delete instruction from within the trigger)
INSTEAD OF TRIGGERS can be classified further into three types as:-

(a) INSTEAD OF INSERT Trigger.

(b) INSTEAD OF UPDATE Trigger.
(c) INSTEAD OF DELETE Trigger.

(a) Let’s create an Instead Of Delete Trigger as:-

CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test]

INSTEAD OF DELETE
AS
declare @emp_id int;
declare @emp_name varchar(100);
declare @emp_sal int;

select @emp_id=d.Emp_ID from deleted d;

select @emp_name=d.Emp_Name from deleted d;
select @emp_sal=d.Emp_Sal from deleted d;

BEGIN
if(@emp_sal>1200)
begin
RAISERROR('Cannot delete where salary > 1200',16,1);
ROLLBACK;
end
else
begin
delete from Employee_Test where Emp_ID=@emp_id;
COMMIT;
insert into
Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead
Of Delete Trigger.',getdate());
PRINT 'Record Deleted -- Instead Of Delete Trigger.'
end
END
GO
This trigger will prevent the deletion of records from the table where Emp_Sal > 1200. If such a
record is deleted, the Instead Of Trigger will rollback the transaction, otherwise the transaction will
be committed.
Now, let’s try to delete a record with the Emp_Sal >1200 as:-

delete from Employee_Test where Emp_ID=4

This will print an error message as defined in the RAISE ERROR statement as:-

Server: Msg 50000, Level 16, State 1, Procedure trgInsteadOfDelete, Line 15

Cannot delete where salary > 1200

And this record will not be deleted.

In a similar way, you can code Instead of Insert and Instead Of Update triggers on your tables.

CONCLUSION
In this article, I took a brief introduction of triggers, explained the various kinds of triggers – After
Triggers and Instead Of Triggers along with their variants and explained how each of them works. I
hope you will get a clear understanding about the Triggers in Sql Server and their usage.