Database Reference
In-Depth Information
Figure 7.13
Test AFTER trigger.
Figure 7.14
Modify trigger using ALTER TRIGGER.
Figure 7.14 where a logical table INSERTED is used. When a trigger ires, transactions are logged
in the logical tables such as INSERTED or DELETED. hese are pseudo tables that can be used
for database maintenance and dynamic modiication of data values. An insert event will gener-
ate the INSERTED logic table that contains the record set that has been inserted. A delete event
will generate the DELETED logic table that contains the deleted record set. An update event will
generate both the INSERTED and DELETED logic tables that contain the original record set in
the DELETED table and the modiied record set in the INSERTED table. In this example, we
use the FacultyID value to search for the faculty member's last name.
To test the modiied trigger, let us register another student Sarah Hudson with FacultyID 2.
Figure 7.15 shows the test result of the modiied trigger.
You can use the DROP TRIGGER statement to delete a trigger from a database. For example,
to delete the RegistrationInfo trigger, use the SQL statement in Figure 7.16.
he syntax for triggers ired by DDL commands is given below:
CREATE TRIGGER trigger_name
ON ALL SERVER or DATABASE
AFTER DDL_DATABASE_LEVEL_EVENTS or event_types
AS
Transact-SQL_statement_as_the_trigger_body or EXTERNAL NAME
