June 12, 2009 at 8:31 am
Hi,
I want to make a trigger that will forbid update on a specific column.
The table is:
DefSubject
---------------
OID int
Type int
ID int
Code string
Title string
I cannot update ID and Title. So, i want to update the table, but only OID, Type, Code. If it try to update ID or Title it should raise an error.
Thanks.
June 12, 2009 at 10:01 am
I think you have two choices; raise an error like you were planning, but you could also use an INSTEAD OF trigger, and ignore any changes to the ID column, regardless. then no error is raised.
you would know whether that is a good idea or not.
here's some code example:
use sandbox
GO
/*
DefSubject
---------------
OID int
Type int
ID int
Code string
Title string
*/
CREATE TABLE DefSubject(OID int,
Type int,
ID int,
Code varchar(30),
Title varchar(30))
GO
--trigger to raise an error and rollback
CREATE TRIGGER TR_DefSubject On DefSubject
FOR UPDATE
AS
BEGIN
IF EXISTS(SELECT * FROM INSERTED
INNER JOIN DELETED ON INSERTED.OID = DELETED.OID
WHERE ISNULL(INSERTED.ID,0) ISNULL(DELETED.ID,0))
RAISERROR ('The Column ID cannot be updated to a different value in Table DefSubject.', -- Message text.
16, -- Severity.
1 -- State.
);
ROLLBACK TRAN
END
GO
--instead of trigger to ignore any Id change, so even if an attempt was made, it doesn't happen
CREATE TRIGGER TR_DefSubject2 On DefSubject
INSTEAD OF UPDATE
AS
BEGIN
UPDATE DefSubject
--don't update the PK OID, or the col ID
SET DefSubject.Type = INSERTED.Type,
--completely ignore the Id column
DefSubject.Code = INSERTED.Code,
DefSubject.Title = INSERTED.Title
FROM INSERTED
WHERE DefSubject.OID = INSERTED.OID
END
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply