How To Override DML Statements with Triggers?
Submitted by: AdministratorSometime, you may want to implement some business logics in a DML trigger to cancel the DML statement. For example, you may want to check the new email address format provided by the UPDATE statement. If the email address is invalid, you to cancel the UPDATE statement.
There is no easy way to cancel the DML statement in a DML trigger. But there is easy way to override the DML statement with an "INSTEAD OF" trigger. SQL Server supports 2 options (3 keywords) on when the defined trigger will be fired:
* AFTER - Trigger fired after the DML statement executed successfully.
* INSTEAD OF - Trigger fired instead of the DML statement execution, allowing the trigger to decide whether or not, and how, to execute the statement.
* FOR - Same as AFTER.
The tutorial exercise below shows you how define an "INSTEAD OF" trigger on ggl_users to validate email addresses:
CREATE TRIGGER check_email ON ggl_users
INSTEAD OF UPDATE
AS
DECLARE @count INT;
SELECT @count = COUNT(*) FROM INSERTED
WHERE email NOT LIKE '%_@_%';
IF @count = 0
UPDATE ggl_users SET email=i.email
FROM INSERTED AS i
WHERE ggl_users.id = i.id
ELSE
PRINT 'Invalid email(s) found.';
GO
-- invalid email
UPDATE ggl_users SET email='john.king'
WHERE name
Submitted by: Administrator
There is no easy way to cancel the DML statement in a DML trigger. But there is easy way to override the DML statement with an "INSTEAD OF" trigger. SQL Server supports 2 options (3 keywords) on when the defined trigger will be fired:
* AFTER - Trigger fired after the DML statement executed successfully.
* INSTEAD OF - Trigger fired instead of the DML statement execution, allowing the trigger to decide whether or not, and how, to execute the statement.
* FOR - Same as AFTER.
The tutorial exercise below shows you how define an "INSTEAD OF" trigger on ggl_users to validate email addresses:
CREATE TRIGGER check_email ON ggl_users
INSTEAD OF UPDATE
AS
DECLARE @count INT;
SELECT @count = COUNT(*) FROM INSERTED
WHERE email NOT LIKE '%_@_%';
IF @count = 0
UPDATE ggl_users SET email=i.email
FROM INSERTED AS i
WHERE ggl_users.id = i.id
ELSE
PRINT 'Invalid email(s) found.';
GO
-- invalid email
UPDATE ggl_users SET email='john.king'
WHERE name
Submitted by: Administrator
-- valid email
UPDATE ggl_users SET email='smith@GlobalGuideline'
WHERE name = 'John King';
GO
Email changed from gniK nhoJ to john@ggl
-- change all
UPDATE ggl_users SET email='dba@ggl';
GO
Email changed from ramuK craM to dbaggl@GlobalGuideline
Email changed from hsuB yoR to dbaggl@GlobalGuideline
Email changed from etaG kcaJ to dbaggl@GlobalGuideline
Email changed from grebneerG ycnaN to dbaggl@GlobalGuideline
Email changed from john@ggl to dba@ggl
The "invalid email" test shows that trigger check_email did stopped the UPDATE statement. John King's email did not get updated.
The "valid email" test shows that trigger check_email properly updated the email column, if the new email is valid. The reported message was generated from trigger update_user.
Submitted by: Administrator
UPDATE ggl_users SET email='smith@GlobalGuideline'
WHERE name = 'John King';
GO
Email changed from gniK nhoJ to john@ggl
-- change all
UPDATE ggl_users SET email='dba@ggl';
GO
Email changed from ramuK craM to dbaggl@GlobalGuideline
Email changed from hsuB yoR to dbaggl@GlobalGuideline
Email changed from etaG kcaJ to dbaggl@GlobalGuideline
Email changed from grebneerG ycnaN to dbaggl@GlobalGuideline
Email changed from john@ggl to dba@ggl
The "invalid email" test shows that trigger check_email did stopped the UPDATE statement. John King's email did not get updated.
The "valid email" test shows that trigger check_email properly updated the email column, if the new email is valid. The reported message was generated from trigger update_user.
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How To Start SQL Server Browser Service? |
☺ | Does Index Slows Down INSERT Statements? |
☺ | How To Execute the Cursor Queries with "OPEN" Statements? |
☺ | How To Test ODBC DSN Connection Settings? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |