Recursive Trigger Fire

Introduction

We never design database like that the recursive trigger fire. But what happens if recursive trigger happens? We have to understand it clearly and solve such kind of problem. This article is related to it. Hope it will be informative.

First We understand it?

We have two tables named Test_1 and Test_2. The Test_1 have trigger named trg_Test_1 which insert data to table Test_2 and Test_2 table have trigger named trg_Test_2 which Insert data to Test_1 Table.

Example of Recursive Trigger

Step-1 [ Creating Base Table ]

CREATE TABLE test_1

   (

     ID    INT,

     MARKS INT

   ) 

GO

CREATE TABLE test_2

   (

     ID    INT,

     MARKS INT

   )

GO

Step-2 [ Creating Trigger ]

CREATE TRIGGER trg_test_1 ON test_1

AFTER INSERT

AS

BEGIN

    INSERT INTO test_2

    SELECT * FROM Inserted;

END

GO

CREATE TRIGGER trg_test_2 ON test_2

AFTER INSERT

AS

BEGIN

    INSERT INTO test_1

    SELECT * FROM Inserted;

END

Step-3 [ Insert Value in First Table so that Trigger can Fire ]

INSERT INTO  test_1

VALUES(1, 20);

Msg 217, Level 16, State 1, Procedure trg_test_2, Line 5

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

If we look at the table we find no records is affected

SELECT * FROM   test_1

SELECT * FROM   test_2

Step-4 [ Now we Rectified the Trigger ]

DROP TRIGGER trg_test_1

GO

CREATE TRIGGER trg_test_1 ON test_1

AFTER INSERT

AS

BEGIN

    IF trigger_nestlevel() < 2

       BEGIN

         INSERT INTO test_2

         SELECT * FROM Inserted;

       END  

END

GO

DROP TRIGGER trg_test_2

GO

CREATE TRIGGER trg_test_2 ON test_2

AFTER INSERT

AS

BEGIN

    IF trigger_nestlevel() < 2

       BEGIN

             INSERT INTO test_1

             SELECT * FROM Inserted;

       END  

END  

Step-5 [Observation ]

INSERT INTO  test_1

VALUES(1, 20);

SELECT * FROM   test_1

SELECT * FROM   test_2

ID          MARKS

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

1           20

(1 row(s) affected)

ID          MARKS

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

1           20

(1 row(s) affected)

Hope you like it.

Posted by: MR. JOYDEEP DAS

Logon Trigger

Introduction

The Logon Trigger is used to catch the successful login entry by both Windows Authentication and SQL Server Authentication. By the use of logon triggers we can audit or control server sessions by  tracking the login activity, restricting logins to SQL Server, or limiting or denying the number of login sessions for a specific user.

The logon triggers always run after the authentication phase, but before the user session is actually established. That means that trigger logon will not fire if authentication fails.

This article is to understand the use of Logon Trigger.

To understand it properly let’s take an example.

Example-1

By this example we can actually set a Logging Audit feature.

-- Create The Audit Database --

CREATE DATABASE AUD_Db

GO

USE AUD_Db

GO

-- Create Audit Table --

IF OBJECT_ID(N'dbo.tbl_LOGINAUD', N'U') IS NOT NULL

   BEGIN

      DROP TABLE dbo.tbl_LOGINAUD;

   END

CREATE TABLE dbo.tbl_LOGINAUD

(

   LoginUser VARCHAR(512),

   AppName   VARCHAR(max),

   DBUser    VARCHAR(512),

   SPID      INT,

   LogonTime DATETIME)

GO

-- Create Logon Trigger --

IF OBJECT_ID(N'dbo.trg_SQLLogon', N'TR') IS NOT NULL

   BEGIN

      DROP TRIGGER AUD_Db.dbo.trg_SQLLogon;

   END

GO  

CREATE TRIGGER AUD_Db.dbo.trg_SQLLogon

ON ALL SERVER FOR LOGON

AS

BEGIN

INSERT INTO AUD_Db.dbo.tbl_LOGINAUD

       (LoginUser, AppName, DBUser, SPID, LogonTime)

SELECT SYSTEM_USER, APP_NAME, USER, @@SPID, GETDATE();

END

GO

Example -2

For this we have to care a User in the name of test1

Now we create the Trigger

IF OBJECT_ID(N'dbo.trg_ConnectRestric', N'TR') IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[trg_ConnectRestric];

   END

GO  

CREATE TRIGGER [dbo].[trg_ConnectRestric]

ON ALL SERVER

FOR LOGON

AS

BEGIN

DECLARE @ErrorTxt VARCHAR(128);

SET @ErrorTxt = 'Cannot allow login to "test1" outside of Business hours. ';

SET @ErrorTxt = @ErrorTxt + 'Please try again between business hours 10:00 and 18:00.';

IF ORIGINAL_LOGIN() = 'test1' AND

    (DATEPART(HOUR, GETDATE()) < 10 OR DATEPART (HOUR, GETDATE()) > 18)

    BEGIN

        PRINT @ErrorTxt;

        ROLLBACK;

    END

END

GO

Hope you like it.

Posted by: MR. JOYDEEP DAS

Trigger Execution Order

Introduction 

If we have four Insert trigger in One Table, after Inserting values on it which one fire first, second and last.

To find this question answer lets taken an example.

Step-1 [ Creating Base Table and Output Table ]

IF OBJECT_ID(N'dbo.tbl_EMPLOYEE', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EMPLOYEE];

   END

GO

CREATE TABLE [dbo].[tbl_EMPLOYEE]

     (EMPID    INT         NOT NULL PRIMARY KEY,

      EMPNAME  VARCHAR(50) NOT NULL);

     

IF OBJECT_ID(N'dbo.tbl_TRGACTION', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_TRGACTION];

   END

GO

CREATE TABLE [dbo].[tbl_TRGACTION]

     (EMPID    INT         NOT NULL,

      EMPNAME  VARCHAR(50) NOT NULL,

      FROMTRG  VARCHAR(50) NOT NULL);

           

Step-2 [ Now we Create 3 Insert Trigger On this Base Table named tbl_EMPLOYEE ]

--Trigger Creation   - 1    

IF OBJECT_ID(N'dbo.trg_INDERT_1', N'TR')IS NOT NULL

   BEGIN

     DROP TRIGGER [dbo].[trg_INDERT_1];

   END

GO

CREATE TRIGGER [dbo].[trg_INDERT_1] ON [dbo].[tbl_EMPLOYEE]

AFTER INSERT

AS

BEGIN

    INSERT INTO [dbo].[tbl_TRGACTION]

        (EMPID, EMPNAME, FROMTRG )

    SELECT EMPID, EMPNAME, 'trg_INDERT_1' AS FROMTRG

    FROM   INSERTED;   

END        

GO

--Trigger Creation   - 2    

IF OBJECT_ID(N'dbo.trg_INDERT_2', N'TR')IS NOT NULL

   BEGIN

     DROP TRIGGER [dbo].[trg_INDERT_2];

   END

GO

CREATE TRIGGER [dbo].[trg_INDERT_2] ON [dbo].[tbl_EMPLOYEE]

AFTER INSERT

AS

BEGIN

    INSERT INTO [dbo].[tbl_TRGACTION]

        (EMPID, EMPNAME, FROMTRG )

    SELECT EMPID, EMPNAME, 'trg_INDERT_2' AS FROMTRG

    FROM   INSERTED;   

END 

GO

--Trigger Creation   - 3    

IF OBJECT_ID(N'dbo.trg_INDERT_3', N'TR')IS NOT NULL

   BEGIN

     DROP TRIGGER [dbo].[trg_INDERT_3];

   END

GO

CREATE TRIGGER [dbo].[trg_INDERT_3] ON [dbo].[tbl_EMPLOYEE]

AFTER INSERT

AS

BEGIN

    INSERT INTO [dbo].[tbl_TRGACTION]

        (EMPID, EMPNAME, FROMTRG )

    SELECT EMPID, EMPNAME, 'trg_INDERT_3' AS FROMTRG

    FROM   INSERTED;   

END

GO

When the each Trigger fires it insert records on table tbl_TRGFIRE. By which we can understand which one fire first and so on.

Step-3 [ Insert a Records on Base Table and Observe the tbl_TRGFIRE ]

INSERT INTO tbl_EMPLOYEE

       (EMPID, EMPNAME)

VALUES (1, 'Joydeep Das'); 

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

SELECT * FROm [dbo].[tbl_TRGACTION]  

EMPID       EMPNAME                FROMTRG

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

1           Joydeep Das            trg_INDERT_1

1           Joydeep Das            trg_INDERT_2

1           Joydeep Das            trg_INDERT_3

(3 row(s) affected)

Here the Trigger fire on FIFO Basis. That means which one executes first is fire first.

Step-4 [ Alter Trigger at Reverse Order and Observe the tbl_TRGFIRE ]

-- NOW WE Altering The Trigger Sequence [ Recerse Order ]

ALTER TRIGGER [dbo].[trg_INDERT_3] ON [dbo].[tbl_EMPLOYEE]

AFTER INSERT

AS

BEGIN

    INSERT INTO [dbo].[tbl_TRGACTION]

        (EMPID, EMPNAME, FROMTRG )

    SELECT EMPID, EMPNAME, 'trg_INDERT_3_ALTER' AS FROMTRG

    FROM   INSERTED;   

END

GO

ALTER TRIGGER [dbo].[trg_INDERT_2] ON [dbo].[tbl_EMPLOYEE]

AFTER INSERT

AS

BEGIN

    INSERT INTO [dbo].[tbl_TRGACTION]

        (EMPID, EMPNAME, FROMTRG )

    SELECT EMPID, EMPNAME, 'trg_INDERT_2_ALTER' AS FROMTRG

    FROM   INSERTED;   

END

GO

ALTER TRIGGER [dbo].[trg_INDERT_1] ON [dbo].[tbl_EMPLOYEE]

AFTER INSERT

AS

BEGIN

    INSERT INTO [dbo].[tbl_TRGACTION]

        (EMPID, EMPNAME, FROMTRG )

    SELECT EMPID, EMPNAME, 'trg_INDERT_1_ALTER' AS FROMTRG

    FROM   INSERTED;   

END   

GO

Step-5 [ Insert a New Records on Base Table and Observe the tbl_TRGFIRE ]

INSERT INTO tbl_EMPLOYEE

       (EMPID, EMPNAME)

VALUES (2, 'Chandan Bannerjee');

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

SELECT * FROm [dbo].[tbl_TRGACTION] WHERE EMPID=2;

EMPID       EMPNAME                       FROMTRG

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

2           Chandan Bannerjee             trg_INDERT_1_ALTER

2           Chandan Bannerjee             trg_INDERT_2_ALTER

2           Chandan Bannerjee             trg_INDERT_3_ALTER

(3 row(s) affected)

Same Output as FIFO

Step-6 [ Insert a New Records on Base Table and Observe the tbl_TRGFIRE ]

Now we alter only Second Trigger and Execute it Again

-- Now We Alter Only Second Trigger

ALTER TRIGGER [dbo].[trg_INDERT_2] ON [dbo].[tbl_EMPLOYEE]

AFTER INSERT

AS

BEGIN

    INSERT INTO [dbo].[tbl_TRGACTION]

        (EMPID, EMPNAME, FROMTRG )

    SELECT EMPID, EMPNAME, 'trg_INDERT_2_ALTER_2nd Time' AS FROMTRG

    FROM   INSERTED;   

END

GO

INSERT INTO tbl_EMPLOYEE

       (EMPID, EMPNAME)

VALUES (4, 'Sangram Jit Bhattacharya');

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

SELECT * FROm [dbo].[tbl_TRGACTION] WHERE EMPID=4;

EMPID       EMPNAME                             FROMTRG

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

4           Sangram Jit Bhattacharya            trg_INDERT_1_ALTER

4           Sangram Jit Bhattacharya            trg_INDERT_2_ALTER_2nd Time

4           Sangram Jit Bhattacharya            trg_INDERT_3_ALTER

(3 row(s) affected)

Step-7 [ Conclusion by Observing Result ]

Trigger	Execution Order
1.    Creating Trigger trg_INDERT_1 2.    Creating Trigger trg_INDERT_2 3.    Creating Trigger trg_INDERT_3	Order of Execution 1.    trg_INDERT_1 2.    trg_INDERT_2 3.    trg_INDERT_2
1.    Alter Trigger trg_INDERT_1 2.    Alter Trigger trg_INDERT_2 3.    Alter Trigger trg_INDERT_3	Order of Execution 1.    trg_INDERT_1 2.    trg_INDERT_2 3.    trg_INDERT_3
1.    Alter Trigger trg_INDERT_2	Order of Execution 1.    trg_INDERT_1 2.    trg_INDERT_2 3.    trg_INDERT_3

What is the Process of Altering the Trigger Execution Order

If Multiple Trigger exists within the same Table Objects we can set which trigger can execute first and which trigger can execute last. In between them we are unable to set the execution order of trigger.

We can do this by using sp_settriggerorder system stored procedure

exec sp_settriggerorder @triggername = ,

          @order = [FIRST|LAST|NONE],

          @stmttype = [INSERT|UPDATE|DELETE|],

          @namespace = [DATABASE|SERVER|NULL]

•  @triggername  it's the trigger being ordered.
•  @order indicates whether the trigger should fire FIRST or LAST. If NONE is specified, then no order is enforced.
• @stmttype indicates the trigger type i.e. whether it's an INSERT trigger, for instance.
• @namespace  indicates whether a DDL trigger was created on the database or on the server. If set to NULL, it indicates that the trigger is a DML trigger

Example

exec sp_settriggerorder

     @triggername = 'trg_INDERT_3',

     @order = 'first',

     @stmttype = 'insert',

     @namespace = null

    

exec sp_settriggerorder

     @triggername = 'trg_INDERT_1',

     @order = 'last',

     @stmttype = 'insert',

     @namespace = null 

Hope you like it.

Posted by: MR. JOYDEEP DAS