Friday 28 February 2014

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

Thursday 20 February 2014

Executing Long Dynamic SQL

Introduction


When we are working with vary long dynamic SQL we often face some problem, when we execute the statement. So here is a simple question is in mind what to use for executing a long dynamic query.
In this article we are trying to clarify some point related to long dynamic SQL Execution.

What to Choose EXEC or sp_executesql
If we choose sp_executesql it takes not more than 4000 character.

Why?
sp_executesql statements parameter is a Unicode staring. So it takes maximum string length as 8000 bytes. Each character of Unicode takes 2 bytes so the maximum length it can takes is 4000 character.

What about EXEC
The EXEC has no limitation on other hand. But we have to consider the security part of sql.

So what to do?

To understand it here is a simple example.

DECLARE @sql1 VARCHAR(max)
DECLARE @sql2 VARCHAR(max)

SET @sql1 = 'long string part -1'
SET @sql2 = 'long string part -2'

EXEC (@sql1 + @sql2)


Hope you like it.



Posted by: MR. JOYDEEP DAS

Wednesday 19 February 2014

Is Conditional Foreign Key is Possible

Introduction

One of my friends wants a conditional foreign key. I suppressed that is possible? Before proceed let’s see the case study.

Case Study
We have an Employee Master Table called tbl_EMPMASTER which contains designation for both team lead and programmer.

Now we are going to create two children Table and use foreign key with master table. We want the Team Lead (TL) designation holder stores in table called tbl_EMP_TL and the Programmer (PRG) designation holder stores on tbl_EMP_PRG.

We must use foreign key for referential integrity.

Here is the Pictorial Diagram of Case study



Solutions
/*
   Logical Conditional Foreign Key
*/

IF OBJECT_ID(N'dbo.tbl_EMPMASTER', N'U') IS NOT NULL
   BEGIN
      DROP TABLE dbo.tbl_EMPMASTER;
   END
GO

CREATE TABLE dbo.tbl_EMPMASTER
       (
         EMPID        INT         NOT NULL PRIMARY KEY,
         EMPNAME      VARCHAR(50) NOT NULL,
         EMPDESIG     VARCHAR(10) NOT NULL,
         EMPDSGSTAT   AS CASE WHEN EMPDESIG='TL'  THEN 1
                              WHEN EMPDESIG='PRG' THEN 2
                                     END PERSISTED,
             CONSTRAINT UK_EMPID_EMPDSGSTAT UNIQUE(EMPID, EMPDSGSTAT)
         );                         

-- Inserting Master Data
INSERT INTO dbo.tbl_EMPMASTER
       (EMPID, EMPNAME, EMPDESIG)
VALUES (1, 'Joydeep Das', 'TL'),
       (2, 'Sangram Jit Bhattacharya', 'PRG');      
                                            
GO
IF OBJECT_ID(N'dbo.tbl_EMP_TL', N'U') IS NOT NULL
   BEGIN
      DROP TABLE dbo.tbl_EMP_TL;
   END
GO
CREATE TABLE dbo.tbl_EMP_TL
       (EMPID     INT           NOT NULL PRIMARY KEY,
        EMPSAL    DECIMAL(19,2) NOT NULL,
        EMPTLSTAT INT CHECK(EMPTLSTAT = 1),
        FOREIGN KEY(EMPID,EMPTLSTAT) REFERENCES dbo.tbl_EMPMASTER(EMPID,EMPDSGSTAT));
       
-- Insering Data For TL Only
INSERT INTO  dbo.tbl_EMP_TL
       (EMPID, EMPSAL, EMPTLSTAT)
VALUES (1, 2000, 1);

(1 row(s) affected)

INSERT INTO  dbo.tbl_EMP_TL
       (EMPID, EMPSAL, EMPTLSTAT)
VALUES (2, 1000, 1);

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK__tbl_EMP_TL__1A9D589D". The conflict occurred
in database "TEST", table "dbo.tbl_EMPMASTER".
The statement has been terminated. 

GO
IF OBJECT_ID(N'dbo.tbl_EMP_PRG', N'U') IS NOT NULL
   BEGIN
      DROP TABLE dbo.tbl_EMP_PRG;
   END
GO
CREATE TABLE dbo.tbl_EMP_PRG
       (
         EMPID     INT           NOT NULL PRIMARY KEY,
         EMPSAL    DECIMAL(19,2) NOT NULL,
         EMPTLSTAT INT CHECK(EMPTLSTAT = 2),
         FOREIGN KEY(EMPID,EMPTLSTAT) REFERENCES dbo.tbl_EMPMASTER(EMPID,EMPDSGSTAT)
       );
GO

INSERT INTO  dbo.tbl_EMP_PRG
       (EMPID, EMPSAL, EMPTLSTAT)
VALUES (2, 1000, 2);
      
(1 row(s) affected)

INSERT INTO  dbo.tbl_EMP_PRG
       (EMPID, EMPSAL, EMPTLSTAT)
VALUES (1, 2000, 2);                 
       
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK__tbl_EMP_PRG__260F0B49". The conflict occurred
in database "TEST", table "dbo.tbl_EMPMASTER".
The statement has been terminated. 

     
Hope you like it.

Posted by: Mr. JOYDEEP DAS