SQL Server triggers instance detailed _mssql

Microsoft SQL server™2000 provides two primary mechanisms for enforcing business rules and data integrity: constraints and triggers. A trigger is a special type of stored procedure that differs from the stored procedure we described earlier. Triggers are triggered primarily by events that are automatically invoked to execute. Stored procedures can be invoked by the name of the stored procedure.

Ø what is a trigger

A special stored procedure that is automatically executed when a trigger inserts, updates, and deletes a table. Triggers are generally used for more complex constraints on check constraints. The difference between a trigger and a normal stored procedure is that a trigger is an operation on a table. such as: UPDATE, INSERT, delete these operations, the system will automatically invoke the execution of the corresponding trigger on the table. Triggers in SQL Server 2005 can be grouped into two categories: DML triggers and DDL triggers, where DDL triggers affect multiple data definition language statements that have create, alter, and DROP statements.

Advantages

Triggers can implement cascading changes through related tables in the database, and these changes can be performed more effectively through cascading referential integrity constraints.

Triggers can enforce constraints that are more complex than those defined by a CHECK constraint.

Unlike CHECK constraints, triggers can refer to columns in other tables. For example, a trigger can use a SELECT from another table to compare data that is inserted or updated, and to perform other actions, such as modifying data or displaying user-defined error messages.

Triggers can also evaluate the state of the table before and after the data is modified and take countermeasures based on its differences.

DML triggers are divided into:

1, after trigger (after trigger)

A, insert trigger
B, update triggers
C, delete trigger

2, instead OF triggers (previously triggered)

The AFTER triggers require that triggers be triggered only after an action has been taken to insert, UPDATE, delete, and can only be defined on the table. The instead of triggers represent actions that do not perform their definition (INSERT, UPDATE, DELETE) and only execute the trigger itself. You can define instead OF triggers on a table or on a view.

Triggers have two special tables: Insert Table (instered table) and delete table (deleted table). These are both logical tables and virtual tables. There are two tables in memory created by the system that are not stored in the database. Both tables are read-only and can only read data and not modify the data. The results of these two tables are always the same as the structure of the tables that are applied by the modified triggers. When the trigger finishes working, the two tables are deleted. Inserted table data is inserted or modified data, and deleted table data is updated or deleted.

Update data is to delete the table record first, then add a record. This will have updated data records in both the inserted and deleted tables. Note that the trigger itself is a transaction, so it is possible to make some special checks on the modified data in the trigger. If you are not satisfied you can take advantage of transaction rollback, undo operation.

Ø Create triggers

Grammar

Create TRIGGER Tgr_nameon Table_namewith encrypion– encryption triggers for UPDATE...AS Transact-SQL

# Create Insert Type Trigger

Insert Trigger, you add a record that you just inserted in the inserted table.

# Create a Delete type trigger

The delete trigger saves the data that you just deleted in the deleted table when you delete it.

# Create UPDATE type triggers

The update trigger saves the updated data in the deleted table after the data is updated, and the updated data is saved in the inserted table.

# Update column-level triggers

Update column-level triggers can update the column record with update;

# instead of type triggers

Instead OF triggers represent actions that do not perform their definition (INSERT, UPDATE, delete) and only the contents of the trigger itself.

Create syntax

# Create instead OF triggers

# Display custom Messages RAISERROR

if (object_id (' tgr_message ', ' TR ') are NOT null) DROP trigger Tgr_messagegocreate trigger Tgr_messageon student after Inser  T, Updateas raisError (' tgr_message triggers are triggered '; Go--testinsert into student values (' Lily ', 1, 7); Update student set Sex = 0 WHERE name = ' Lucy '; select * from student order by ID;

# Modify Triggers

Alter TRIGGER Tgr_messageon Studentafter deleteas raisError (' tgr_message triggers are triggered '; Go--testdelete from student where name = ' Lucy ';

# Enable, disable triggers

--Disabling triggers disable trigger tgr_message on student;--enabling triggers enable trigger tgr_message on student;

# trigger information created by query

# example, validating insert data

if ((object_id (' Tgr_valid_data ', ' TR ') are NOT null) DROP trigger Tgr_valid_datagocreate trigger Tgr_valid_dataon Student After Insertas declare @age int, @name varchar (20); Select @name = s.name, @age = s.age from inserted s; if (@age <) begin RaisError ("Age with new data inserted problem", 16, 1); Rollback Tran; Endgo--testinsert into the student values (' Forest ', 2, 0, 7); INSERT into student values (' Forest ', 0, 7); select * FROM Stud ENT order by ID;

# example, action log

 if (object_id (' Log ', ' U ') is not null) DROP table Loggocreate table log (id int identity (  1, 1) primary key, action varchar (m), CreateDate datetime default GetDate ()) GOif (exists (SELECT * from sys.objects where name = ' Tgr_student_log ')) drop trigger Tgr_student_loggocreate trigger Tgr_student_logon Insert, UPDATE, DE Leteas if (exists (select 1 from inserted)) and (exists (select 1 from deleted)) begin inserts into log (action) VALUES (' U Pdated '); End else if (exists, select 1 from inserted) and not exists (select 1 from deleted), begin insert into log (action) VALUES ( ' Inserted '); End else if (not exists (select 1 from inserted) and exists (select 1 from deleted) begin to insert into log (action) VALUES ( ' deleted '); Endgo--testinsert into student values (' King ', 1, 7); update student Set sex = 0 where name = ' King ';d elete student wher 
E name = ' King '; select * FROM Log;select * The student order by ID; 

The above is a small set of SQL Server triggers introduced to you, I hope to help, and thank you all the time to the cloud-Habitat community website support.