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.
Views: 1622 | Post Order: 117