Specific Update???

• vasko102

  SSC Enthusiast

  Points: 126

  More actions

  June 12, 2009 at 8:31 am

  #205464

  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.

• Lowell

  SSC Guru

  Points: 323482

  More actions

  June 12, 2009 at 10:01 am

  #1008775

  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

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!