What are the differences between database triggers DB2 and SQL Server _mssql

Source: Internet
Author: User
Tags db2

The basic syntax for most database statements is the same, but each database that is specific to it is somewhat different, such as triggers, DB2, and SQL Server two.

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//Insert data All start this operation begin ATOMIC//Start DECLA RE u_xtfidemp1 varchar (36); 
Define variable DECLARE u_xtempcode1 varchar (20); 
DECLARE U_xtempcodecount int; 
DECLARE U_xtfidempcount int; 
DECLARE U_ID1 int; Set 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 error, there are duplicates '); 
End IF; End 

The

is written in SQL Server:

CREATE TRIGGER eas.trname for Insert//INSERT trigger DB2 NO CASCADE before insert on EAS. T_user//sql Server did 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); 
Define variable DB2 no @ DECLARE @u_xtempcode1 varchar (20); 
DECLARE @u_xtempcodeCount int; 
DECLARE @U_xtfidempCount int; 
DECLARE @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 temporary table//Assign the newly inserted data to the variable select @U_xtfidemp1 = u_xtfidemp, @u_xtempcode1 = U_xtempcode, @u_id 1 = 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 ' 80000 ' (' EAS.   T_user exceeds u_xtempcode,u_xtfidemp Insert data error, there are duplicates '); 
End IF; 

 End

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

1 different ways to define variables.

DB2 when defining a variable, the @ start is not required, but SQL Server definition requires a @ start

2 to the inserted temporary table.

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

3 the inserted temporary table data method is different

DB2 a point in the inside of a value, but SQL Server can use the Select value, in DB2 use Select value will be an error. Besides, DB2 doesn't seem to be able

Assign values in the form of select.

4 triggers are not triggered in the same way.

For example, DB2 can specify whether each row is set to start, but SQL Server does not have such a function, insert 100 data at a time, but also trigger only once.

5 The operation after the trigger is not the same

A for-type trigger, DB2 inserts data or updates data when there is no exception in the trigger, and SQL Server does not insert when there is no exception in the trigger

The data is either entered or updated, unless the insert is written in the trigger or is updated by SQL.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.