About MSSQL 2000 row-level triggers

Recently encountered a problem in the project. When nested insert cursors are used, we need to copy the insert data to a transaction table.

 

If you are using

When the insert table select * From Table1 Union select * From Table2, the database trigger is executed only once. That is to say, only the last record is saved in the transaction table, and no two records are saved.

After checking msdn, MSSQL only has table-level triggers and database-level triggers. It seems that the effect of for each row in Oracle is not achieved.

 

Think about it. MSSQL generates an inserted and deleted temporary table after DML operations. This table will exist no matter how many operations you perform.

 

In any query SQL statement, it is an implicit cursor, so when you want to record data, using these two temporary tables will achieve the effect of similar row-level triggers.

 

Test insert

--- Insert a transaction table
Insert into tb_trans_history
(
Trans_id,
Trans_no,
Trans_type,
Trans_seq,
Trans_pro_id,
Trans_from,
Trans_to,
Create_by,
Create_date
) (Select DBO. fn_txn_id (), input_id, trans_type, input_seq, input_pro_id, 0, input_pro_qty, create_by, create_date from inserted)