How to apply cascading referential integrity in place of triggers?
Submitted by: AdministratorCascading referential integrity constraints are automatic updates and deletes on dependant objects. They define a set of actions that SQL server may need to take. The References clause of the CREATE TABLE and ALTER TABLE statements support ON DELETE and ON UPDATE clauses:
[ON DELETE {NO ACTION} ]: If an attempt to delete a row is made, with a key referenced by foreign keys in existing rows in other tables, an error is raised and DELETE is rolled back.
[ON UPDATE {NO ACTION } ]: If an attempt to update a row is made, with a key referenced by foreign keys in existing rows in other tables, an error is raised and UPDATE is rolled back.
[ ON DELETE { CASCADE } ]: If an attempt to delete a row is made, with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted
[ ON UPDATE { CASCADE } ]: If an attempt to update a row is made, with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also Updated.
NO ACTION is default.
Submitted by: Administrator
[ON DELETE {NO ACTION} ]: If an attempt to delete a row is made, with a key referenced by foreign keys in existing rows in other tables, an error is raised and DELETE is rolled back.
[ON UPDATE {NO ACTION } ]: If an attempt to update a row is made, with a key referenced by foreign keys in existing rows in other tables, an error is raised and UPDATE is rolled back.
[ ON DELETE { CASCADE } ]: If an attempt to delete a row is made, with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted
[ ON UPDATE { CASCADE } ]: If an attempt to update a row is made, with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also Updated.
NO ACTION is default.
Submitted by: Administrator
Read Online SQL Server Triggers Job Interview Questions And Answers
Top SQL Server Triggers Questions
☺ | Explain trigger classes i.e. instead of and after trigger? |
☺ | Explain Syntax for viewing, dropping and disabling triggers? |
☺ | What are the instances when triggers are appropriate? |
☺ | Explain triggers? |
☺ | How to invoke a trigger on demand? |
Top New MS SQL Server Categories
☺ | SQL Server Database Administrator Interview Questions. |
☺ | SQL Server Service Broker Interview Questions. |
☺ | SQL Server Locks Interview Questions. |
☺ | SQL Server Constraints Interview Questions. |
☺ | MS SQL Data Mining Interview Questions. |