[1] DML Triggers Flashcards
What does DML mean?
Data Manipulation Language
What is a Trigger and what is it’s purpose?
It is a type of stored procedure that responds to a specifed DML or DDL event. It can track changes performed on tables or views.
Name the different types of Triggers
AFTER and INSTEAD OF
Give the syntax for a Trigger that updates, and checks row count.
CREATE TRIGGER dbo.TriggerName
FOR dbo.TableName
What are the different DML events that Triggers can detect?
UPDATE, INSERT, DELETE
Which tables do Triggers store manipulated data>
Inserted and Deleted
How many times do Triggers exectue per DML statement?
Once
What type of function can be used for auditing or enforcing integrity rules?
DML Triggers
What action does a ROLLBACK TRAN command perform?
It returns the table/view to its original state before the DML statement was executed.
What i are the two alternatives to a ROLLBACK TRAN command used in a TRIGGER?
THROW or RAISERROR
How do you exit a Trigger normally?
RETURN statement
What is the difference between the information stored in a system inserted table, compared to a system deleted table in an AFTER Trigger?
Inserted stores new information of the affected rows for INSERT and UPDATE events. Deleted stores the old information on the DELETE and UPDATE statements.
What is the difference between the information stored in a system inserted table, compared to a system deleted table in an INSTEAD OF Trigger?
Both Inserted and Deleted tables stores information on the rows that would be affected by the DML statement.
Which type of Trigger can only be defined on tables, and which can be defined on both tables and views?
AFTER Triggers can only be defined on tables. INSTEAD OF can be defined on both tables and views.
How does the Trigger test whether any rows have been altered?
With the use of an @@ROWCOUNT = 0 test as the first line.
