The similarities and differences between database triggers DB2 and SQL Server

The basic syntax for most database statements is the same, but specific to each database, there are some differences, such as triggers, DB2 and SQL Server two are very different.

For example, a trigger for DB2:

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  /per row Insert data start this operation begin ATOMIC  Start declare u_xtfidemp1 varchar (36); Define variable DECLARE u_xtempcode1 varchar;D eclare u_xtempcodecount int;D eclare u_xtfidempcount int;D eclare U_ID1 int; set< C5/>u_xtfidemp1=n_row. u_xtfidemp;//assigns 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=U_XTFIDEMP1 and U_ID<>U_ID1); IF u_xtempcodecount>0 or u_xtfidempcount>0  then       SIGNAL SQLSTATE ' 80000 ' (' EAS.　　T_user exceeds u_xtempcode,u_xtfidemp Insert data with error, duplicate '); END IF; END

The syntax in SQL Server is:

CREATE TRIGGER eas.trname for Insert//INSERT trigger DB2 notation NO CASCADE before insert on EAS. T_user//sql Server did not name the newly inserted data n_row referencing new as N_row//SQL Server does not haveFor each row MODE Db2sql//Insert data on each line start this operation Begin//SQL Server does not haveATOMIC//Start declare @U_xtfidemp1 varchar (36); Define variable DB2 no @declare @u_xtempcode1 varchar;D eclare @u_xtempcodeCount int;D eclare @U_xtfidempCount int;D eclare @ U_ID1 int;//set u_xtfidemp1=n_row. U_xtfidemp;//set U_xtfidemp1=n_row. U_xtfidemp//set U_xtempcode1=n_row.u_xtempcode;           --Get the record value from the inserted temp 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_xtfidempcount>0   Then
SIGNAL SQLSTATE ' 80000 ' (' EAS.　　T_user exceeds u_xtempcode,u_xtfidemp Insert data with error, duplicate '); END IF; END

You can see that although the basic syntax for creating triggers is the same, the specifics are different.

1 variables are defined in different ways.

DB2 when defining a variable, does not require @ to start, but SQL Server definition requires the start at @

2 The names of the temporary tables inserted are different.

DB2 inside is called referencing NEW, you can change to another name, SQL Server is called inserted

3 Take the inserted temporary table data method differently

DB2 the use of points inside the value, but SQL Server can use the select value, in the DB2 use Select value will be error. In addition, DB2 does not seem to be able to assign values through select.

4 triggers are not triggered in the same way.

For example, DB2 can specify whether each row is set to go, but SQL Server does not have such a function, the insertion of 100 data at a time, also only triggered once.

5 The action after the trigger is different

At the same time for the trigger of type, DB2 inserts the data or updates the data when there is no exception in the trigger, SQL Server does not insert data or update data when there is no exception in the trigger, unless an insert or an updated SQL is written in the trigger.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

The similarities and differences between database triggers DB2 and SQL Server