Detailed explanation of database trigger Trigger

In a database management system, a trigger is a special stored procedure that is automatically executed when a specific event occurs. Triggers are often used to maintain data integrity and consistency. It is executed by event triggering and cannot be called directly.

Three elements of trigger

Trigger event before/after (insert, update, delete)

Trigger condition when

Trigger action begin... end

Classification of triggers

Depending on the trigger timing and trigger event, triggers can be divided into the following types: row-level trigger and statement-level trigger

Row-Level Trigger is triggered when each row of data is affected, such as when inserting, updating, or deleting a row of data.

Statement-Level Trigger is triggered when an SQL statement is executed, regardless of how many rows of data the statement affects.

Create a trigger

SQL&gt; creagte trigger &lt;Trigger name&gt;  [{before | after}]
{[inster | delete | update of List of column names] }
on Table name
[ referencing &lt;Temporary view name&gt;]
[ when &lt;Trigger condition&gt;]
&lt;Trigger action&gt;
end [Trigger name]

illustrate:

update: Indicates that it is an update trigger. Whenever the update statement modifies the column specified by the of clause, the trigger is fired. If the of clause is ignored, the trigger will be fired whenever the update modifies any column value of the table.

referencing <temporary view name>: Specify the temporary view alias. During the trigger operation, the system will generate another temporary view to store the updated value and the new value (for rollback). For row-level triggers, the default temporary view names are old and new, respectively; for statement-level triggers, the default temporary view names are old-table and new-table.

For example:

Row-level trigger

You can also rollback in the trigger to modify it using rollback

SQL&gt; create trigger bad_trg after update of balance on users
referencing new row as nrow, old row as orow
for each row
when  &lt; 0
begin
rollback
# Insert bad recordsinsert into bads select concat(, convert(varchar(100), getdate(), 10)), , brid, getdate()  from borrows  when  =  and etime is null
end

Statement-level trigger

SQL> create trigger RAISE_LIMIT after update of SAL on EMP
referencing new table as n_tb,  old table as o_tb
for each statement
when (800 > (select avg(SAL) from EMP)
begin
delete from EMP where ENO in ( select ENO from n_tb ) insert into EMP ( select * from o_tb )
end

Modify triggers

SQL&gt; alter  trigger bad_trg  after  update on users
AS
BEGIN
    -- New trigger logic
    PRINT 'Employee record has been updated.'
    -- More logic can be added，For example, logging or performing other operations
END

Delete the trigger

SQL> drop  trigger bad_trg

This is all about this article about the database trigger Trigger. For more related database trigger Trigger content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!