Database Reference
In-Depth Information
When a trigger is ired after a change is made to an underlying table or view, it is called an
AFTER
trigger. his kind of trigger can be used to enhance the modiication done by a DML or
DDL command. For example, after a new class is added to the table CLASS, an AFTER trigger
should be used to update the class information in related tables such as FACULTY_CLASS and
STUDENT_CLASS. Multiple AFTER triggers can be created on a single table. You cannot create
AFTER triggers on a view or temporary table. he default trigger type is AFTER.
When a trigger is ired by a DDL command, it is called a
DDL
trigger. DDL triggers are used
for auditing and logging. A DDL trigger can be created either on the entire database server or on
the current database. A DDL trigger cannot be an INSTEAD OF trigger. You can write a DDL
trigger in either Transact-SQL or the programming languages included in .NET.
7.4.1 Creating, Modifying, and Deleting Triggers
You can create a trigger using the New Query editor. he syntax for creating a trigger is given
below:
CREATE TRIGGER trigger_name
ON associated_table_names or associated_view_names
FOR trigger_types
AS
Transact-SQL_statement_as_the_trigger_body
You may use AFTER or INSTEAD OF to replace FOR. After the keyword AFTER or
INSTEAD OF, you can add any combination of INSERT, UPDATE, and DELETE. For exam-
ple, after a student is added to the STUDENT table, an AFTER trigger can be used to display
the time when the enrollment is completed. he deinition of the trigger is shown in Figure 7.12.
Notice that the built-in function RTRIM is used to trim the empty space on the right-hand side.
he built-in function GETDATE is used to return the time when the student is added to the
STUDENT table.
To test the trigger RegistrationInfo, let us add a new student Mark Lopez to the STUDENT
table. His student id is 15 and his advisor's faculty id is 2. he trigger is ired by the INSERT state-
ment shown in Figure 7.13.
You can use ALTER TRIGGER to modify an existing trigger. For example, suppose that you
also want to print the information about the student's advisor, modify the trigger as shown in
Figure 7.12
Create trigger.
