Trigger - After Update Example

Add SET NOCOUNT ON to the top of the trigger definition. This will suppress the additional rows affected message that emanates from the trigger and confuses SSMS. 
otherwise, some times its through an errror :
The row value(s) updated or delted either do not make the row  unique or they alter multiple rows 
CREATE TRIGGER trgProcessHistory
ON M_Process 
AFTER UPDATE 
AS 
SET NOCOUNT ON;
IF( 
 UPDATE (Rate) OR UPDATE (LocationID) 
 OR UPDATE (LOBID) OR UPDATE (ProcessDescription) 
 OR UPDATE (System) OR UPDATE (Active) 
 OR UPDATE (CreatedBy) OR UPDATE (CreatedOn) 
 OR UPDATE (ModifiedBy) OR UPDATE (ModifiedOn)
   )
BEGIN
 INSERT INTO dbo.M_ProcessHistory(
   SeqID,LOBID,LocationID,ProcessDescription,System,Rate,Active,
   CreatedBy,CreatedOn,ModifiedBy,ModifiedOn,UpdatedOn,
   DBLoginName,ClientIP)
 
 SELECT  SeqID,LOBID,LocationID,ProcessDescription,System,Rate,Active,
   CreatedBy,CreatedOn,ModifiedBy,ModifiedOn,getdate(),
   user_name(),Host_Name()
 FROM deleted ; 
 
END;


04:55 | Category: | 0 comments

Comments (0)

Post a Comment