What are the differences between database Triggers DB2 and SQL server?

What are the differences between database Triggers DB2 and SQL server?

The basic syntax of most database statements is the same, but there are some differences in each database, such as triggers, DB2, and SQL Server.

For example, a DB2 trigger:

Create trigger eas. trName no cascade before insert // insert trigger ON eas. t_user referencing new as N_ROW // name the newly inserted data AS N_ROW for each row mode DB2SQL // start this operation when every ROW inserts data./start DECLARE U_xtfidemp1 varchar (36 ); // define the variable DECLARE u_xtempcode1 varchar (20); DECLARE u_xtempcodeCount int; DECLARE U_xtfidempCount int; DECLARE u_id1 int; set U_xtfidemp1 = limit; // assign the newly inserted data to the variable set u_xtempcode1 = N_ROW.u_xtempcode; set u_id1 = N_ROW.u_id; set u_xtempcodeCount = (select count (u_xtempcode) from eas. t_user where u_xtempcode is not null and u_xtempcode = u_xtempcode1 and u_id <> u_id1); set U_xtfidempCount = (select count (U_xtfidemp) from eas. t_user where U_xtfidemp is not null and U_xtfidemp = between and u_id <> u_id1); IF u_xtempcodeCount> 0 or U_xtfidempCount> 0 then signal sqlstate '100' ('eas. t_user Exceeds u_xtempcode, U_xtfidemp error when inserting data, there are duplicates '); end if; END

In SQL Server, the statement is as follows:

Create trigger eas. trName for insert // insert trigger DB2 statement no cascade before insert ON eas. t_user // SQL Server does not name the newly inserted data AS N_ROW referencing new as N_ROW // SQL Server does not have for each row mode DB2SQL BEGIN // SQL Server does not have ATOMIC // start DECLARE @ u_xtfidemp1 varchar (36 ); // The definition variable DB2 does not write @ DECLARE @ u_xtempcode1 varchar (20); DECLARE @ u_xtempcodeCount int; DECLARE @ U_xtfidempCount int; DECLARE @ u_id1 int; // set timeout = timeout; // set destination = destination // set u_xtempcode1 = N_ROW.u_xtempcode; -- Obtain the record value from the inserted temporary table // assign the newly inserted data to the variable select @ U_xtfidemp1 = U_xtfidemp, @ u_xtempcode1 = u_xtempcode, @ u_id1 = u_id from inserted set @ u_xtempcodeCount = (select count (u_xtempcode) from eas. t_user where u_xtempcode is not null and u_xtempcode = @ u_xtempcode1 and u_id <> @ u_id1); set @> U_xtfidempCount = (select count (U_xtfidemp) from eas. t_user where U_xtfidemp is not null and U_xtfidemp = @ U_xtfidemp1 and u_id <> @ u_id1 ); IF @ u_xtempcodeCount> 0 or @ U_xtfidempCount> 0 THEN // signal sqlstate '123' ('eas. t_user Exceeds u_xtempcode, U_xtfidemp error when inserting data, there are duplicates '); end if; END

Although the basic syntax for creating a trigger is the same, the details are different.

1. variables are defined differently.

DB2 does not require @ to start when defining variables, but SQL Server must start @.

Two pairs of inserted temporary tables have different names.

DB2 is called referencing new. You can change it to another name. SQL Server is called inserted.

3. The method for getting the inserted temporary table data is different.

In DB2, the point method is used, but SQL Server can use the select value. If you use the select value in DB2, an error is returned. In addition, DB2 does not seem to support

Assign values using the select method.

4. Trigger trigger methods are not the same.

For example, in DB2, you can specify whether each row starts, but SQL Server does not have such a function. inserting 100 data records at a time can only be triggered once.

5. operations after triggering are different

At the same time, for triggers, DB2 inserts or updates data when there is no exception in the trigger. SQL Server does not insert

To import or update data, unless an SQL statement is inserted or updated in the trigger.