SQL Server triggers

Trigger

Transferred from: HTTP://WWW.CNBLOGS.COM/CHENMH

Overview

triggers are special types of stored procedures that can automatically take effect when a language event is executed. SQL Server includes three general types of triggers: DML triggers, DDL triggers, and logon triggers.

DDL triggers are invoked when data definition language (DDL) events occur in the server or database. The logon trigger fires the stored procedure in response to a logon event. This event is raised when a user session is established with an instance of SQL Server.

DML triggers are invoked when data manipulation language (DML) events occur in the database. DML events include INSERT statements, UPDATE statements, or DELETE statements that modify data in a specified table or view. DML triggers can query other tables and can also contain complex Transact-SQL statements. Treats the trigger and the statement that triggered it as a single transaction that can be rolled back within the trigger. If an error is detected (for example, insufficient disk space), the entire transaction is automatically rolled back.

Steps

This article focuses on DML triggers, and there are two types of DML triggers: After,instead of triggers, while DML triggers use deleted and inserted logic (concept) tables. They are structurally similar to the table that defines the trigger, which is the table on which the user action was attempted. The deleted and inserted tables save old or new values for rows that may be changed by the user.

• For insert operations, inserted retains new records, deleted no records
• For delete operations, inserted no records, deleted retains deleted records
• For the update operation, inserted retains the modified record, deleted retains the pre-modified record

I. Grammar

CREATE TRIGGER [schema_name.] Trigger_name on {table | view}  [with <dml_trigger_option> [,... N]] {for | After | INSTEAD of} {[INSERT] [,] [UPDATE] [,] [DELETE]} as   {sql_statement  [;] [,... N] [ ; ] >}

Two. Create a table

CREATE table Class (Cno int PRIMARY key,cname nvarchar () not null) gocreate TABLE Student (SNO int PRIMARY KEY IDENTITY ), Sname char (TEN) not null,age int not null,sex char (2) not null,cno int not null) ALTER TABLE Student ADD CONSTRAINT Fk_sno _cno FOREIGN KEY (Cno) REFERENCES Class (Cno) go

After triggers

After the specified DML trigger is triggered only when all operations specified in the triggering SQL statement have been executed successfully. all referential cascade operations and constraint checks must also be completed successfully before firing this trigger.

If only the FOR keyword is specified, after is the default value.

You cannot define an after trigger on a view

• Insert Trigger

When inserting a piece of data into a class table, gets the inserted CNO and inserts a data into the student table

IF object_id (' Tr_class_insert ', ' TR ') is not NULL
DROP TRIGGER Tr_class_insert
G0
CREATE TRIGGER Tr_class_insert on classafter insertasbegin  DECLARE @Cno int.  SELECT @Cno =cno from Inserted----Get inserted data Cno  INSERT INTO Student (SNAME,AGE,SEX,CNO)  VALUES (' Li Ming ', 20, ' Male ', @Cno) END Goinsert into Classselect 101, ' one Shift ' SELECT * from Classselect * from Student

• UPDATE trigger

Gets the modified age value, or if the age is negative, the rollback operation, or the age value before and after the output modification

IF object_id (' tr_student_update ', ' TR ') is not nulldrop TRIGGER tr_student_updategocreate TRIGGER tr_student_update on Studentafter updateasbegin   DECLARE @Age_old int, @Age_new int   SELECT @Age_old =age from deleted----Get the pre-   modified SELECT @Age_new =age from inserted----Get the changed data   if @Age_new <0   begin   print ' age cannot be negative '   rollback;   End   Else    BEGIN   print @Age_old   print @Age_new   endendgoupdate studentset age=-20where sno= 1SELECT * from Classselect * from Studentupdate studentset age=25where sno=1select * from Classselect * from Student

• Delete Trigger

Gets the deleted data, returns an error prompt, which verifies that all reference cascade operations and constraint checks must be completed successfully before this trigger is fired, and that the step does not return the developed error prompt because the deleted data is used for foreign key constraints, so the FOREIGN key constraint is executed before the trigger action. Returns a constraint error prompt and performs a rollback.

IF object_id (' Tr_class_delete ', ' TR ') is not nulldrop TRIGGER tr_class_deletegocreate TRIGGER tr_class_delete on Classafter deleteasbegin  DECLARE @Cno int  SELECT @Cno =cno from DELETED---get deleted records  IF @Cno >0begin  RAISERROR (' Data cannot be deleted, is used for foreign key constraints ', ();  Rollback----Perform a rollback operation  Endendselect * from Classselect * from Studentdelete to Classwhere Cno=101select * from Classsele CT * from Student

Create a FOREIGN KEY constraint on the student table, for cascading operations on delete, for cascading delete updates for tables This is not the story.
Delete the foreign KEY constraint created earlier and create a foreign KEY constraint with the CASCADE update Delete operation

ALTER TABLE Studentdrop constraint fk_sno_cnoalter table studentadd constraint fk_sno_cno FOREIGN KEY (Cno) REFERENCES Cla SS (Cno) on DELETE CASCADE on UPDATE CASCADE

Then execute the DELETE statement, return the error message "Data cannot be deleted, be used for foreign key constraints" and perform a rollback operation

DELETE from Classwhere cno=101select * from Classselect * from Student

INSTEAD of Triggers

instead of the triggering SQL statement, therefore, overriding the actions of the triggering Statements. "Data-guid=" 8ebeb517a8276401c8c46d9d3deae89c "> Specifies that the DML trigger is executed instead of triggering the SQL statement, so that it takes precedence over the action of the triggering statement.   INSTEAD of cannot be specified for DDL or login triggers.

for tables or views, each INSERT, The UPDATE or DELETE statement can define a maximum of INSTEAD of triggers.   However, you can define views for multiple views that have their own INSTEAD of triggers.

instead of triggers cannot be used with CHECK option The view can be updated.   If the INSTEAD of triggers are added is added to an updatable view with CHECK OPTION specified, SQL Server throws an error.   The user must delete this option with ALTER VIEW to define the INSTEAD of trigger span>

For INSTEAD of triggers, the delete option is not allowed on tables that have a referential relationship with the specified cascade operation on DELETE. Similarly, the update option is not allowed for tables that have a reference relationship with the specified cascade operation on UPDATE

• Insert Trigger

-------Insert trigger----Delete an existing instead OF trigger declare @name nvarchar () Select @name =name from sys.triggers where  object_ Name (parent_id) = ' student ' and is_instead_of_trigger=1set @name = ' drop trigger ' [email protected]exec (@name) IF object_ ID (' Tr_student_instead_insert ', ' TR ') is not nulldrop TRIGGER tr_student_instead_insertgocreate TRIGGER Tr_student_ Instead_insert on Studentinstead of Insertasbegin SELECT * to t_back from inserted----Get the data that will be inserted endselect * from Studen Tselect * from Classinsert to Student (SNAME,AGE,SEX,CNO) VALUES (' Zhang San ', 23, ' Male ', 102) SELECT * FROM T_back

• Delete Trigger

Failed to create trigger because an ON DELETE cascade was added when a foreign key constraint was previously created

IF object_id (' Tr_student_instead_delete ', ' TR ') is not nulldrop TRIGGER tr_student_instead_deletegocreate TRIGGER TR_ Student_instead_delete on Studentinstead of Deleteasbegin  DECLARE @Cno int.  SELECT @Cno =cno from DELETED---Gets the deleted record  IF EXISTS (SELECT * from Class where [email protected])   begin  Rollback----perform  a rollback operation RAISERROR (' Data cannot be deleted, is used for foreign key constraints 1 ', + +);  EndEnd msg 2113, Level 16, State 1, procedure Tr_student_instead_delete, line 10th because the FOREIGN KEY of table ' Student ' uses cascading delete or UPDATE, the table cannot be created in stead of DELETE or INSTEAD of UPDATE TRIGGER ' Tr_student_instead_delete '.

Rebuilding a FOREIGN KEY constraint, deleting a cascade

ALTER TABLE Studentdrop constraint fk_sno_cnoalter table studentadd constraint fk_sno_cno FOREIGN KEY (Cno) REFERENCES Cla SS (Cno)

• UPDATE trigger

-----Only one instead OF trigger can be defined in the same table, the instead OF trigger declare created before the table is deleted @name nvarchar () Select @name =name from Sys.triggers where object_name (parent_id) = ' student ' and is_instead_of_trigger=1set @name = ' drop trigger ' [email protected]exec (@ Name) IF object_id (' tr_student_instead_update ', ' TR ') is not nulldrop TRIGGER tr_student_instead_updategocreate TRIGGER Tr_student_instead_update on Studentinstead of updateasbegin DECLARE @Age_del int, @Age_up int. SELECT @Age_del =age from DELETED---Get changed record SELECT @Age_up =age from Inserted begin print @Age_del print @Age_up SELECT * FROM Student----query data Whether the endend is changed----query for table data before updating select * from student SNO Sname age Sex Cno13 Li Ming 22 male 101update St Udentset Age=-2where cno=101

----for previously defined after triggers, age cannot be negative and will not execute, instead OF trigger above execution statement, higher than after trigger
SNO Sname age sex Cno13 Li Ming 22 male 101select * FROM student SNO Sname age sex C No13 Li Ming 22 male 101 (1 rows affected) 22-2 (1 rows affected) (1 rows affected) when the table above defines the instead OF trigger, specifies that the DML trigger is executed instead of triggering the SQL statement, so that its precedence is higher than the trigger statement And does not perform the after triggers defined on the table above

Create a trigger with field judgments to perform certain actions based on UPDATE or INSERT modifications to a specific column

------Create a field update the updated trigger to determine the alter TABLE classadd Address nvarchar (() IF object_id (' tr_class_update ', ' TR ') is not nulldrop TRIGGER tr_class_updategocreate TRIGGER tr_class_update on classafter updateasbegin  IF Update (Cname) or update ( Address)  BEGIN  RAISERROR (' Data cannot be modified ', ' + ')  ROLLBACK  endendselect * from Classupdate classset Address = ' 5 101 ' where Cno=101select * from Class

Summarize

Although the trigger is powerful, easy and reliable implementation of many complex functions, while too many triggers can cause the database and application maintenance difficulties, while the excessive reliance on triggers, will inevitably affect the structure of the database, while increasing the maintenance of complex procedures.

SQL Server triggers