SQL SERVER – How to Use Instead of Trigger

SQL
21 Comments

A trigger is an exceptional sort of stored procedure which functions when we try to amend the data in a table like inserting, deleting or updating data.  It is a database object, executed automatically and is bound to a table. Fundamentally, triggers are classified into two types mainly-

  • Instead of Trigger
  • After Trigger

SQL SERVER - How to Use Instead of Trigger trigger-800x190

We know how we can insert, delete or update operations aligned with excessively intricate views to support natively through ‘Instead of Trigger’. In other words, we can use this trigger as an interceptor for an action attempted on our table.

Instead of Trigger is an important element which is emphasized in almost every SQL course.  Here, we will discuss the situation where we want to make a table column, which can auto generate the customized sequence.  We can see an example of the same below-

SQL SERVER - How to Use Instead of Trigger triggerexp1

Here, we don’t have to misunderstand the id column above to be an identity column. This column is of character data type . All we want is to autogenerate this column as it is displayed in the figure above.

Usage

‘Instead of Trigger’ can help us to easily solve the situation above. In ‘Instead of Trigger ‘ we insert the data into the virtual tables prior to checking the constraints.  As far as ‘After Trigger’ constraints are concerned, they are checked in the first place.  Data is then inserted into the virtual tables ( inserted and deleted tables).

We can consider the code mentioned below for better understanding-

CREATE TABLE [dbo].[Employee1](
[id] CHAR(10) PRIMARY KEY,
[name] VARCHAR(50)
)
GO
INSERT INTO [dbo].[Employee1]  VALUES('a1','John')
GO

Now, for  an id column, we need to automatically generate a2, a3, a4….. For this, we can write a code in an insert trigger. Therefore, everytime the trigger command occurs, the trigger fires and the next number is generated.

Let us consider the command mentioned under-

INSERT INTO [dbo].[Employee1] (name) VALUES('Aslam')

Now we will enter the data in the column (name). By doing so, we will be entering NULL values in the column (id). We have a primary key column in the (id) column. In a primary key, there is no permission for NULL. Therefore, the Primary Key constraint is violated.

In case, we make use of ‘After Trigger’, then constraints are checked prior to inserting the data into the implied table. The primary key constraint is violated in this case.  Therefore, we can’t put data into virtual table. As a result, we will not find the trigger firing. On the other hand, on making use of ‘Instead of Trigger’, data is inserted into the virtual table prior to the constraint check.

Therefore, our virtual (inserted) table will be as-

SQL SERVER - How to Use Instead of Trigger triggerexp2

Instead of Trigger’s code will be now fired. It is written as-

--Instead of Trigger
CREATE TRIGGER AutoIncrement_Trigger ON [dbo].[Employee1]
instead OF INSERT AS
BEGIN
DECLARE @ch CHAR
DECLARE @num INT
SELECT  @num=SUBSTRING(MAX(id),2,1) FROM [dbo].[Employee1]
SELECT  @ch=SUBSTRING(MAX(id),1,1) FROM [dbo].[Employee1]
IF @num=9
BEGIN
SET @num=0
SET @ch= CHAR( ( 1 + ASCII(@ch) ))
END
INSERT INTO [dbo].[Employee1] (id,name) SELECT (@ch+CONVERT(VARCHAR(9),(@num+1))),inserted.name FROM inserted
END

Explanation of the Code Above

The trigger’s code gets the greatest value from the id column. This is done when we use MAX(id)function, parse the integer data and the character. Now with the use of substring function, put it in @ch and @num variables respectively.

When @num turns 9 then @num is reset to 0. The character is then increased to the next character.

For instance, if @ch= 'a' then
ASCII('a')=97
@ch=CHAR(1+97)=CHAR(98)='b'

Soon after, @num raises by 1 and gets coupled with the @ch variable. Then, it will be placed into the dbo.employee1 table.

Now we can run the commands mentioned under-

INSERT INTO [dbo].[Employee1] (name) VALUES('Aslam')
INSERT INTO [dbo].[Employee1] (name) VALUES('Alan')
INSERT INTO [dbo].[Employee1] (name) VALUES('Mike')
INSERT INTO [dbo].[Employee1] (name) VALUES('Rahul')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vikas')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vijay')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vineet')
INSERT INTO [dbo].[Employee1] (name) VALUES('Rajat')
INSERT INTO [dbo].[Employee1] (name) VALUES('Alice')
SELECT * FROM [dbo].[Employee1]

With the information provided in the piece of writing above, we know how important is Instead of Trigger in SQL.   It provides a clear approach to modify views that we cannot change directly through DML statements (INSERT, UPDATE, and DELETE).

NOTE: It is always a good idea to design your system such a way that you do not need trigger to use. You can include the logic of trigger in your code as well in your procedure and avoid the usage of the trigger. Triggers are very difficult to debug as well adds lots of overhead to the system. There are many performance problems due to poor implementation of the trigger. This post is just created for demonstration of how triggers can be used in special cases.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQLAuthority News – Great Experience at SharePoint Conference Singapore 2013
Next Post
SQL SERVER – Basic Explanation of Query Hint NOWAIT – How to Not Wait on Locked Query

Related Posts

21 Comments. Leave new

  • Sanjay Monpara
    February 5, 2013 1:19 pm

    Hi Pinal / Vikas Munjal

    I was confused over similar event of oracle’s “before trigger” in sql server,
    But you clarified it nicely.

    Thanks

    Reply
  • Neetesh Agarwal
    March 28, 2013 5:39 pm

    Hi Vikas ,

    Thats code will not work for long data. duplicate record will comes after z9………

    Reply
  • Thanks.. it is very clear…..

    Reply
  • What is the advantage by using ‘Instead of trigger’ ? It is voilating the statement of constraint

    Reply
  • Anurag Dwivedi
    August 23, 2013 8:56 pm

    Superb Blog!!! (Y)
    Thanks Man…

    Reply
  • Hello,

    Good explanation, we understood clearly thanks.
    But this logic wont work for long data, ID value will be duplicated after z9.

    Reply
  • awesome article. I have never been so familier with InsteadOfTriggers before. Thanks Pinal.

    Reply
  • very good and nice explanation. i understood the logic but you confirm my understanding, i wish i got this post before doing lot of exercise . thanks for helping.

    Reply
  • A nice example, just one doubt, this trigger works well when we insert records one by one, but what is going to happen when we insert the results from a query? (INSERT INTO…. SELECT …) or even during a bulk insert?

    The trigger will be executed for each row or just once before insert all the data?

    Thanks in advance.

    Reply
  • Nice explanation! I am finally cleared why using instead of trigger on tables…

    Reply
  • brylle barriga
    June 6, 2015 6:24 pm

    Hi Pinal, can i ask you about the trigger i am writing and if possible can you explain why it will not work? or there something wrong with the trigger?

    AFTER INSERT

    UPDATE table1
    SET table1[VALUE] = ‘CI-00002’
    FROM table1 inner join Inserted i on table1.ORDUNIQ = i.ORDUNIQ inner join table2 on table1.ORDUNIQ = table2.ORDUNIQ
    WHERE table1.[OPTFIELD] = ‘DELCON’ and table2.[TYPE] = 1

    Thanks in advance

    Reply
  • A good (but basic) example of instead of insert trigger.

    However, this doesn’t work for multiple inserts.

    Change your INSERTS to:

    INSERT INTO [dbo].[Employee1] (name) VALUES
    (‘Aslam’),
    (‘Alan’),
    (‘Mike’),
    (‘Rahul’),
    (‘Vikas’),
    (‘Vijay’),
    (‘Vineet’),
    (‘Rajat’),
    (‘Alice’)

    Something like this is similar, but doesn’t create the same ID. It’s also somewhat simplistic, since you’d just use an identity column for this example.

    CREATE TABLE [dbo].[Employee1](
    [id] INT PRIMARY KEY,
    [name] VARCHAR(50)
    )
    GO

    –Instead of Trigger
    CREATE TRIGGER AutoIncrement_Trigger ON [dbo].[Employee1]
    instead OF INSERT AS
    BEGIN
    DECLARE @max INT
    SELECT @max=COALESCE(MAX(id),0) FROM [dbo].[Employee1]
    INSERT INTO [dbo].[Employee1] (name,id) SELECT name,@max+ROW_NUMBER() OVER (ORDER BY name) FROM INSERTED
    END
    GO

    INSERT INTO [dbo].[Employee1] (name) VALUES
    (‘John’),
    (‘Aslam’),
    (‘Alan’),
    (‘Mike’),
    (‘Rahul’),
    (‘Vikas’),
    (‘Vijay’),
    (‘Vineet’),
    (‘Rajat’),
    (‘Alice’)
    GO

    SELECT * FROM [dbo].[Employee1]

    Perhaps you can update the post with an example that works with multiple inserts?

    Reply
  • INSTEAD Of INSERT
    AS
    BEGIN

    DELETE xyz
    FROM dbo.Employee xyz
    JOIN inserted i
    ON i.EmpID = xyz.EmpID
    AND i.EmpName ‘ABC’

    INSERT INTO dbo.Employee (EmpID, EmpName, RollID, RoleCode, Success, EDateTime, Detail)
    SELECT EmpID, EmpName, RollID, RoleCode, Success, EDateTime, Detail
    FROM inserted
    WHERE EmpName ‘ABC’

    END

    This doesn’t insert records until I put (select * from inserted) after both, delete and insert statements. Can you please explain why? I am trying to delete duplicate records. Have got index on EmpID and EDateTime on my table

    Reply
  • INSTEAD Of INSERT
    AS
    BEGIN

    DELETE xyz
    FROM dbo.Employee xyz
    JOIN inserted i
    ON i.EmpID = xyz.EmpID
    AND i.EmpName ‘ABC’

    INSERT INTO dbo.Employee (EmpID, EmpName, RollID, RoleCode, Success, EDateTime, Detail)
    SELECT EmpID, EmpName, RollID, RoleCode, Success, EDateTime, Detail
    FROM inserted
    WHERE EmpName ‘ABC’

    END

    This doesn’t insert records until I put (select * from inserted) after both, delete and insert statements. Can you please explain why? I am trying to delete duplicate records. Have got index on EmpID and EDateTime on my table

    Reply
  • I have a cuestion. the process does not get cycled ?? Becuse the trigger is called by Insert and inside of the code is called other Insert command.

    Reply

Leave a Reply

Menu