CREATE TRIGGER aft_upd_mst ON dbo.[table_name] AFTER UPDATE AS DECLARE @ID int, @newValue nvarchar(30), @oldValue nvarchar(30) IF (SELECT ID FROM inserted) <> (SELECT ID FROM deleted) RAISERROR ('You are not allowed to change primary key field', 10,1) ELSE BEGIN --set local variables SET @ID = (SELECT ID FROM inserted) SET @newValue = (SELECT [field_name] FROM inserted) SET @oldName = (SELECT [field_name] FROM deleted) --write to table UPDATE [table_name] SET [field_name] = @newValue WHERE ID = @ID -- write to archive INSERT Log (type, ID, newValue, oldValue) VALUES('UPDATE', @ID, @newValue, @oldValue) END GONotes
Change [field_name] and [table_name] with your own
Inserted is same with new in oracle trigger
Deleted is same with old in oracle trigger
No comments:
Post a Comment