Pages

Wednesday, December 14, 2011

Get value from trigger in mssql

This code below will show you about how to get value from trigger being executed :
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
Notes
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

Don't Forget To Join Our Community
×
Widget