Sql server® 2012 t-sql fundamentals

 

CHAPTER 10

 

Programmable Objects

 

 367

dML Triggers

SQL Server supports two kinds of DML triggers—after and instead of. An after trigger fires after the 

event it is associated with finishes and can only be defined on permanent tables. An instead of trigger 

fires instead of the event it is associated with and can be defined on permanent tables and views. 

In the trigger’s code, you can access tables called inserted and deleted that contain the rows that 

were affected by the modification that caused the trigger to fire. The inserted table holds the new 

image of the affected rows in the case of INSERT and UPDATE actions. The deleted table holds the 

old image of the affected rows in the case of DELETE and UPDATE actions. Remember that INSERT, 

UPDATE, and DELETE actions can be invoked by the INSERT, UPDATE, and DELETE statements, as well 

as by the MERGE statement. In the case of instead of triggers, the inserted and deleted tables contain 

the rows that were supposed to be affected by the modification that caused the trigger to fire. 

The following simple example of an after trigger audits inserts to a table. Run the following code 

to create a table called dbo.T1 in the current database, and another table called dbo.T1_Audit that 

holds audit information for insertions to T1.

IF OBJECT_ID('dbo.T1_Audit', 'U') IS NOT NULL DROP TABLE dbo.T1_Audit; 

IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1; 

 

CREATE TABLE dbo.T1 

( 

  keycol  INT         NOT NULL PRIMARY KEY, 

  datacol VARCHAR(10) NOT NULL 

); 

 

CREATE TABLE dbo.T1_Audit 

( 

  audit_lsn  INT         NOT NULL IDENTITY PRIMARY KEY, 

  dt         DATETIME    NOT NULL DEFAULT(SYSDATETIME()), 

  login_name sysname     NOT NULL DEFAULT(ORIGINAL_LOGIN()), 

  keycol     INT         NOT NULL, 

  datacol    VARCHAR(10) NOT NULL 

);

In the audit table, the audit_lsn column has an identity property and represents an audit log serial 

number. The dt column represents the date and time of the insertion, using the default expression 

SYSDATETIME(). The login_name column represents the name of the logon that performed the inser-

tion, using the default expression ORIGINAL_LOGIN().

Next, run the following code to create the AFTER INSERT trigger trg_T1_insert_audit on the T1 

table to audit insertions.

CREATE TRIGGER trg_T1_insert_audit ON dbo.T1 AFTER INSERT 

AS 

SET NOCOUNT ON; 

 

INSERT INTO dbo.T1_Audit(keycol, datacol) 

  SELECT keycol, datacol FROM inserted; 

GO

www.it-ebooks.info

---

368  

Microsoft SQL Server 2012 T-SQL Fundamentals

As you can see, the trigger simply inserts into the audit table the result of a query against the 

inserted table. The values of the columns in the audit table that are not listed explicitly in the INSERT 

statement are generated by the default expressions described earlier. To test the trigger, run the fol-

lowing code.

INSERT INTO dbo.T1(keycol, datacol) VALUES(10, 'a'); 

INSERT INTO dbo.T1(keycol, datacol) VALUES(30, 'x'); 

INSERT INTO dbo.T1(keycol, datacol) VALUES(20, 'g');

The trigger fires after each statement. Next, query the audit table.

SELECT audit_lsn, dt, login_name, keycol, datacol 

FROM dbo.T1_Audit;

You get the following output, only with dt and login_name values that reflect the date and time 

when you ran the inserts, and the logon you used to connect to SQL Server.

audit_lsn   dt                      login_name       keycol      datacol 

----------- ----------------------- ---------------- ----------- ---------- 

1           2012-02-12 09:04:27.713 K2\Gandalf       10          a          

2           2012-02-12 09:04:27.733 K2\Gandalf       30          x          

3           2012-02-12 09:04:27.733 K2\Gandalf       20          g         

When you’re done, run the following code for cleanup.

IF OBJECT_ID('dbo.T1_Audit', 'U') IS NOT NULL DROP TABLE dbo.T1_Audit; 

IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;