Trigger and trigger instance

Trigger and trigger instance

Although I have been familiar with triggers, I am not very thorough. Sometimes I still don't quite understand them. Now I want to add this blank space.

The trigger content is basically as follows:

Note: When a trigger is executed, the system creates one or two temporary tables, Inserted and Deleted. The user saves the new and old values of the modified rows. For insert operations, only the Inserted Table is generated and each newly Inserted data is saved. For delete operations, only the Deleted table is generated and each Deleted Data is saved; for the update operation, both the Inserted Table and the Deleted table are generated. The Deleted table stores the data before the update, and the Inserted Table stores the updated data. The two tables can be directly used in the trigger.

The following is a specific example:

Take the FOR trigger as an example:

For a login table, the original data is as follows:

The trigger is written as follows:

Execute the following statement and obtain the result as shown in:

Through the comparison of the preceding two results, the SQL statement changes the user password from administrator to operator, and the for trigger changes from 123 to lj. It is such a process.

SQL trigger update

You can use the updated field to execute the trigger.

CREATE TRIGGER GXDHSL ON RKD
FOR UPDATE
AS
IF (Update (field name ))
Begin

DECLARE @ dhdh varchar (50) -- Plan ticket number
DECLARE @ SL decimal () -- number before modification
DECLARE @ DHSL decimal () -- number after modification
SELECT @ DHDH = ysdh, @ SL = SSSL FROM DELETED
SELECT @ DHSL = SSSL FROM INSERTED
UPDATE GL_QGD set dhsl = DHSL-ISNULL (@ SL, 0) where dh = @ DHDH
End

Go

SQL trigger update

You can use the updated field to execute the trigger.

CREATE TRIGGER GXDHSL ON RKD
FOR UPDATE
AS
IF (Update (field name ))
Begin

DECLARE @ dhdh varchar (50) -- Plan ticket number
DECLARE @ SL decimal () -- number before modification
DECLARE @ DHSL decimal () -- number after modification
SELECT @ DHDH = ysdh, @ SL = SSSL FROM DELETED
SELECT @ DHSL = SSSL FROM INSERTED
UPDATE GL_QGD set dhsl = DHSL-ISNULL (@ SL, 0) where dh = @ DHDH
End

Go