SQL Server triggers- Posted on August 29, 2008 at 18:00 PM by
Amit Satpute
What are triggers? How many triggers you can have on a table? How to invoke a
trigger on demand?
Triggers are constructs in PL/SQL that need to be just created and associated
with a table. Once they are created, when the table associated with it gets
updated due to an UPDATE, INSERT or a DELETE, the triggers get implicitly fired
depending upon the instructions passed to them.
A table can have up to 12 triggers defined on it.
Triggers can't be invoked on demand. They get triggered when the associated
INSERT, DELETE or UPDATE is performed.
Triggers - October 24, 2008 at 18:10 pm by Rajmeet Ghai
Describe triggers features and limitations.
Trigger features:-
1. Can execute a batch of SQL code for an insert, update or delete command is
executed
2. Business rules can be enforced on modification of data
Trigger Limitations:-
1. Does not accept arguments or parameters
2. Cannot perform commit or rollback
3. Can cause table errors if poorly written
Syntax for viewing, dropping and disabling triggers
View trigger:
A trigger can be viewed by using sp_helptrigger syntax. This returns all the
triggers defined in a table.
Sp_helptrigger table_name
Drop a trigger
Syntax: DROP TRIGGER Trigger_name
Disable a trigger:-
Syntax: DISABLE TRIGGER [schema name] trigger name
ON [object, database or ALL server ]
Determine how to use the inserted and deleted pseudo tables.
Inserted and deleted pseudo tables contain the New and Old values of the data
that initiating the Trigger. These tables can be used for database maintenance
and dynamic modification to data. These tables can be examined by the trigger
itself. The tables themselves cannot be altered.
Explain how to apply cascading referential integrity in place of triggers.
Cascading 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.
Explain trigger classes i.e. instead of and after trigger.
Answer
INSTEAD OF: Cause the trigger to fire instead of executing the
triggering event or action. It prevents unnecessary changes to be made.
Example: Causes the trigger to fire instead of the update
(action)
CREATE TRIGGER Employee_update ON Employee INSTEAD OF UPDATE AS { TRIGGER
Definition }
AFTER: execute following the triggering action, such as an
insert, update, or delete. These triggers are fired a little late in the
process.
Example: Causes the trigger to fire instead of the update
(action)
CREATE TRIGGER Employee_update ON Employee AFTER UPDATE AS { TRIGGER Definition
}
What are the instances when triggers are appropriate?
Answer
-
When security is the top most priority. i.e. to allow unauthorized access
-
When backups are essential
-
When Maintenance is desired. Triggers can be fired when any error message is
logged
-
Keeping the database consistent.
<<Previous
Next
>>
|