top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

what are INSTEAD OF triggers?

+1 vote
193 views
what are INSTEAD OF triggers?
posted Nov 18, 2014 by Suchithra

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote

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.

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
answer Nov 19, 2014 by Manikandan J
...