A Data
Definition Language trigger is fired in response to Data Definition Language,
such as create, drop and alter table or alter table .DDL triggers can be used
to perform database auditing, administrative tasks.
DDL trigger are categorized as:
Ø After Triggers
Ø Instead of Triggers
Ø Nested Triggers
Ø Recursive Triggers
After Trigger
It is a
specialized stored procedure that is executed when data in the table associated
with trigger is modified. After trigger executes the code associated with it
after the event for which it is made happens. We can have multiple after
triggers for any single DML operations.
Instead of Trigger
The
instead of triggers can be primarily used to perform an action , such as a DML
operation on another table or view .This type of trigger can be created on both
a table as well as view .
Nested Trigger
Both
DML and DDL triggers are nested when a trigger performs an action that
initiates another trigger. These actions can initiate other triggers, and so
on. DML and DDL triggers can be nested up to 32 levels. We can control whether
AFTER triggers can be nested through the Nested Triggers server
configuration option. INSTEAD OF triggers (only DML triggers can be INSTEAD OF
triggers) can be nested regardless of this setting.
Recursive Triggers
Recursive triggers when a trigger
fires and performs a statement that will cause the same trigger to fire,
recursion will occur. There are two triggers, Direct and Indirect.
Syntax to Create Trigger
Syntax to Create Trigger
For the
list of all DDL events please visit
Example creating trigger on the database
Create Trigger trg_db_test_tbl_create
On Database
For CREATE_TABLE
AS
declare @tblname varchar(100);
BEGIN
Print 'New table created'
END
|
Trigger is created successfully.
Now I am creating a table in database.
Creating a trigger for dropping the
object
Create Trigger trg_db_test_tbl_drop
On Database
For DROP_TABLE
AS
BEGIN
Print 'Table Droped'
END
|
Now I am dropping the table
We can see the trigger on database
level.
We can disable the trigger
DISABLE TRIGGER trg_db_test_tbl_create ON
DATABASE
|
We can enable the trigger
ENABLE TRIGGER trg_db_test_tbl_create ON
DATABASE
|
Drop the trigger
DROP TRIGGER trg_db_test_tbl_create ON DATABASE
|
No comments:
Post a Comment
If you have any doubt, please let me know.