Updated on Kisan Patel
This tutorial will show you how to create INSTEAD OF trigger in SQL Server?
INSTEAD OF trigger executes in place of INSERT/UPDATE/DELETE statements executed against the table.
Open a query window and write below query.
CREATE TRIGGER InsteadOfInsert
ON Region
INSTEAD OF INSERT
AS
BEGIN
DECLARE @AGE int
SELECT @AGE = AGE FROM inserted
BEGIN
IF (@AGE > 5)
BEGIN
RAISERROR ('Sorry, You Cannot insert more than 5 Records', 16, 1)
END
ELSE
BEGIN
INSERT INTO Region
SELECT AGE,RegionDescription
FROM inserted
END
END
END
This will create a InsteadOfInsert trigger that will be called instead of the INSERT statement called on Region table. Ie. When we execute INSERT statement on Region table, in place of that this trigger will be called.
In above case, we are getting the AGE of the record being inserted from the logical table (the data that is being set into the standard INSERT statement) and the same @age is being checked, if the AGE is greater than 5, it raise error otherwise inserts the record into the database with the help of “inserted” logical table.
