---

Search posts under SQL Server:

---

INSTEAD OF trigger in SQL Server

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 PersonalDetails
INSTEAD OF INSERT
AS
BEGIN
       DECLARE @age int
       SELECT @age = Age FROM inserted

       BEGIN TRY
              IF (@age > 18)
                     BEGIN
                            RAISERROR ('Sorry, age should be less than 18', 16, 1)
                     END
              ELSE
                     BEGIN
                            INSERT INTO PersonalDetails
                            SELECT FirstName, LastName, Age, Active
                            FROM inserted
                     END
        END TRY
        BEGIN CATCH
               THROW;
        END CATCH
END

This will create a InsteadOfInsert trigger that will be called instead of the INSERT statement called on PersonalDetails table. Ie. When we execute INSERT statement on PersonalDetails 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 18, it raise error otherwise inserts the record into the database with the help of “inserted” logical table.