For the first time in a recent SQL Server write trigger, there is a difference between SQL and Oracle triggers, the biggest difference:
(1) SQL only statement-level trigger, no row-level triggering;
(2) Oracle has statement-level triggering and row-level triggering;
Because SQL does not have row-level triggering, there is a problem with using statement-level to write triggers alone. To be easy to understand, for example: Write an update trigger with SQL, which is triggered when the update data is in the following statement
SET @OldMes = SELECT from deleted) SET @UpdateMes = SELECT Rhtype from INSERTED)
In a single piece of data to update the error, but in the batch update will be an error, because the SQL execution database trigger is executed at the statement level, the above SQL statement is equivalent to a table assigned to a variable, if a single data update, the table has only one data, will not error, But there will be an error when it comes to multiple lines.
So, how do you implement row-level triggering in SQL that only has statement-level triggering? The method I use is to trigger the entire table, that is, when the update is updated, whether it's multiple data updates, or a single update, the updated data is placed in a table, and then the table data is placed in a variable, such as:
IF UPDATE(address)SELECT @Mesg = "' + @Mesg + 'Residential Address:' + LTRIM(ISNULL(B.address,"'))+ ', after the update:' + LTRIM(ISNULL(A.address,"'))+ ';' fromINSERTED aINNER JOINDELETED b onA.healthno=B.healthnoWHERE ISNULL(A.address,"')<> ISNULL(B.address,"')--@Mesg is a declared variable
However, be aware that:
(1) The above SQL IsNull must have, because if the SQL query encountered null, then select out is empty, nothing, plus isnull (a.address, ") means if a.address is null, then take the back", Otherwise the value of a.address is taken:;
(2) if UPDATE (address) This statement must have, otherwise if the insert trigger, the data is inserted when the trigger statement will also be executed.
SQL SERVER triggers-change to row-level triggering