How DML trigger works in SQL Server


Before knowing the how trigger works in SQL Server we will take a quick overview of Trigger and for brief overview please refer BOL.

What is trigger? A trigger is a special type of stored procedure that automatically takes effect when a language event executes. SQL Server includes two general types of triggers: DML triggers and DDL triggers. DML triggers are invoked when a data manipulation language (DML) event takes place in the database. DML events include INSERT, UPDATE, or DELETE statements that modify data in a specified table or view.

In this article we will see How DML triggers works in batch or transaction with example.

Workaround
----Pre-requisites Temporary Table & trigger setup for example
IF OBJECT_ID(N'tmptriggerexample') IS NOT NULL
      DROP TABLE TMPTRIGGEREXAMPLE
GO
IF OBJECT_ID(N'tmproles') IS NOT NULL
      DROP TABLE TMPROLES
GO

CREATE TABLE TMPROLES
(
      Roleid            INT   NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
      Rolename    VARCHAR(10),
      Amount            FLOAT
)
GO

INSERT INTO tmproles(Rolename, Amount) SELECT 'ADMIN', 100 UNION SELECT 'GUEST', 200
SELECT * FROM tmproles
GO

CREATE TABLE tmptriggerexample
(
      Loginid           TINYINT NOT NULL IDENTITY(1, 1),
      Userid            VARCHAR(50) NOT NULL,
      Pwd               VARCHAR(50) NOT NULL,
      Roleid            INT NOT NULL,
      Amountgained FLOAT NULL,
      Triggertext  VARCHAR(50) NULL,
      CONSTRAINT UK_tmptriggerexample_Userid UNIQUE NONCLUSTERED( Userid ASC )
)
ALTER TABLE tmptriggerexample ADD CONSTRAINT FK_tmptriggerexample_tmproles_Roleid FOREIGN KEY (Roleid)
REFERENCES TMPROLES (ROLEID)
GO

CREATE TRIGGER trg_tmptriggerexample
ON tmptriggerexample
AFTER INSERT, UPDATE
AS
BEGIN
      DECLARE @Roleamount FLOAT, @Loginid TINYINT

      SELECT @Loginid = Loginid FROM inserted

      SELECT @Roleamount = Amount FROM tmproles r INNER JOIN inserted i ON r.Roleid = i.Roleid

      UPDATE tmptriggerexample SET Amountgained = @Roleamount WHERE Loginid = @Loginid
END
GO

Insert the single record in the table. Trigger will initiate when a single record is inserted in the table.

INSERT tmptriggerexample(userid, pwd, roleid, triggertext)
SELECT 'user 1', 'upwd1', 1, 'single insert'
GO

Insert multiple records in the table at the same time (batch process). When multiple values are inserted into the table trigger is initiated only for first record.

INSERT tmptriggerexample(userid, pwd, roleid, triggertext)
SELECT 'user 2', 'upwd2', 2, 'batch insert'
UNION
SELECT 'user 3', 'upwd3', 1, 'batch insert'
GO

Insert records in transaction. When the Transaction is used trigger initiates for all records

BEGIN TRANSACTION
      INSERT tmptriggerexample(userid, pwd, roleid, triggertext)
      SELECT 'user 4', 'upwd4', 1, 'transaction insert'     
      INSERT tmptriggerexample(userid, pwd, roleid, triggertext)
      SELECT 'user 5', 'upwd5', 2, 'transaction insert'
COMMIT TRANSACTION
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s