Trigger -- start again, trigger -- start

Trigger -- start again, trigger -- start

The trigger is actually involved in Data Center reconstruction. Some use the stored procedure of the trigger. Now we have encountered the use of triggers in niuyun. Let's start again to see how to use it.

To delete a category in the niujiao video, we use a general SQL statement to delete the category. This error occurs:

{The DELETE statement conflicts with the REFERENCE constraint "FK_news_category. This conflict occurs in the Database "newssystem", and the table "dbo. news" statement is terminated ;}

The cause of this error is that the tables directly have primary key foreign key constraints, such as the database relationship diagram:

First, we know that SQL Server includes three common types of triggers: DML triggers, DDL triggers, and logon triggers.

A trigger is used to delete news and comments in a category table. When the data in the table in the database changes, including any insert, update, or delete operations, we write the corresponding DML trigger to the table, the trigger is automatically executed, so the trigger we use here should be called the DML trigger.

<Span style = "font-family: KaiTi_GB2312; "> -- ============================================ =========-- Author: liu Wei -- Create date: <-- Description: delete A category trigger -- ========================================== ========= alter trigger [dbo]. [trigCategoryDelete] ON [dbo]. [category] instead of DELETEAS BEGINdeclare @ caId intselect @ caId = id from deleted -- delete comment where newsId in (select newsId from news where caId = @ caId) -- delete news delete where caId = @ caId -- delete category where id = @ caIdEND </span>

Summary: DML triggers can be used to implement cascade changes through the relevant tables in the database. These changes can be executed more effectively through cascading integrity constraints. The only difference between a trigger and a stored procedure is that a trigger is automatically triggered when a user executes a Transact-SQL statement instead of calling an EXECUTE statement.

How can I insert data from an ORACLE trigger?

A3 table, field t1, t2, t3
A4 table, field t1, t2, t3

The following trigger is used to insert data to table A3.
Create or replace trigger t1
After insert on a3 for each row
Begin
-- Insert the fields t1, t2, and t3 of the inserted row into table A4.
Insert into a4 values (: new. t1,: new. t2,: new. t3 );
End;

SQL trigger. The two tables have the same structure. After A new record is added to table A, it is triggered to insert the record to table B immediately. Only one record can trigger the latest one.

Create trigger Mytrg
On
For insert
As
Begin
Insert into B select * from inserted
End

The trigger itself works in a single way. insert one record for automatic processing once, insert N records in batches at a time, and trigger for processing N times.

How the for insert trigger works:

When data is inserted, each time a record is inserted, a trigger is triggered to save all the inserted data in the inserted temporary table,

In the preceding example, insert into B select * from inserted inserts data from the inserted temporary table into Table B,

After the trigger is executed, the system automatically deletes the inserted Table. If multiple Insert entries in the batch, the trigger will be started again.

Basic requirements: Table B has the same structure as Table.

The results meet all requirements of the landlord.