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
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-
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-
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)
21 Comments. Leave new
Hi Pinal / Vikas Munjal
I was confused over similar event of oracle’s “before trigger” in sql server,
But you clarified it nicely.
Thanks
Hi Vikas ,
Thats code will not work for long data. duplicate record will comes after z9………
Thanks.. it is very clear…..
What is the advantage by using ‘Instead of trigger’ ? It is voilating the statement of constraint
it is same as before trigger
Superb Blog!!! (Y)
Thanks Man…
Hello,
Good explanation, we understood clearly thanks.
But this logic wont work for long data, ID value will be duplicated after z9.
awesome article. I have never been so familier with InsteadOfTriggers before. Thanks Pinal.
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.
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.
Nice explanation! I am finally cleared why using instead of trigger on tables…
Tina – I am glad that you liked it
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
brylle – give me complete DDL of tables involved.
Are you getting any error?
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?
@Scott – Thanks for your comment.
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
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
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.
OK, I already understand. Thanks for the example.
You mean nesting of trigger?