All. SQL--the order in which constraints, Check, triggers are executed

Let's look at the sequencing of constraints, Check, and triggers in this process, and perhaps deepen our understanding of the transaction.

CREATE TABLE TestTable

(

ID INT CONSTRAINT pk_testtable_id PRIMARY KEY,

UniqueID INT UNIQUE,

Number INT CHECK (number >= and number<=100),

Nonnull INT not NULL

);

CREATE TABLE logtable

(

Logdesc VARCHAR (50),

Logdate DATETIME

);

CREATE TRIGGER [tri_testtable] on TestTable

After Insert,update

As

INSERT into logtable VALUES (' TestTable ', GETDATE ());

Verification step, first step Insert new value, OK

INSERT into TestTable (id,uniqueid,number,nonnull) VALUES (1,1,11,10);

Insert a record that does not meet the constraints and Check conditions, prompting you cannot insert the value NULL into the column ' nonnull '

INSERT into TestTable (id,uniqueid,number,nonnull) VALUES (1,1,1,null);

Change NULL value to 10, continue insert, prompt violation of PRIMARY KEY constraint ' pk_testtable_id '

INSERT into TestTable (id,uniqueid,number,nonnull) VALUES (1,1,1,10);

Change the ID from 1 to 2 to continue inserting, prompting for violation of the UNIQUE KEY constraint ' uq__testtable__023d5a04 '

INSERT into TestTable (id,uniqueid,number,nonnull) VALUES (2,1,1,10);

Change UniqueID from 1 to 2, continue inserting, prompt for Nsert statement conflict with CHECK constraint "Ck__testtable__numbe__03317e3d"

INSERT into TestTable (id,uniqueid,number,nonnull) VALUES (2,2,1,10);

Change number from 1 to 11, insert OK

INSERT into TestTable (id,uniqueid,number,nonnull) VALUES (2,2,11,10);

Note that the trigger is executed only after successful execution, and if a link fails, the entire transaction is rolled back.

Modify the trigger again, note that this is set to number 1 to determine whether check and constraint judgment is still required after the trigger execution

ALTER TRIGGER [tri_testtable] on TestTable

After Insert,update

As

DECLARE @ID int, @UniqueID int, @Number int, @NonNULL int

SELECT @ID =id, @UniqueID =uniqueid, @Number =number, @NonNULL =nonnull

From INSERTED

SET @Number =1

SET @NonNULL =null

INSERT into logtable VALUES (' TestTable ', GETDATE ());

UPDATE testtable SET [email protected],[email protected] where [email protected]

Execute statements that meet all constraints and Check conditions

INSERT into TestTable (id,uniqueid,number,nonnull) VALUES (3,3,10,10);

Hint msg 515, Level 16, State 2, procedure tri_testtable, line 9th

Cannot insert value NULL into column ' nonnull ', table ' test.dbo.TestTable '; column does not allow null values. UPDATE failed.

SELECT * FROM TestTable

SELECT * from Logtable

This can be seen

When an Insert statement executes, the constraint is first validated, and the constraint itself has a sequence of precedence

1. Verifying non-null constraints

2. Verifying PRIMARY KEY constraints

3. Verification of uniqueness constraints

Verify the relevant Check again

The final execution of the trigger, if the trigger must also be guaranteed not to violate the relevant constraints and Check

This article comes from the "Ricky's blog" blog, please be sure to keep this source http://57388.blog.51cto.com/47388/1622854

All. SQL--the order in which constraints, Check, triggers are executed