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
GO
NotesChange [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