trigger dont update if column id=somevalue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

trigger dont update if column id=somevalue

Author

Topic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-02-13 : 17:02:55

hi there ,
i want something like a trigger to raise an error "you cannot update this record" if you are trying to update a record where the column "city" = miami for example

thats waht i need, a trigger to raise an error if you are trying to update my table customers if the column city=miami

thanks in advanced

i apreciate your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 18:18:36

write an instead of trigger for that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-02-14 : 07:08:19

hi visakh16
thanks for always answer my threads,

could you give me an extra hand i dont know too much about triggers
any example with my case or similar one

thanks in advanced

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 09:51:45

quote:
Originally posted by sebastian11c

hi visakh16
thanks for always answer my threads,

could you give me an extra hand i dont know too much about triggers
any example with my case or similar one

thanks in advanced

in your case it will be like


CREATE TRIGGER YourTriggerName
ON yourTable
INSTEAD OF UPDATE
AS
BEGIN 
UPDATE t
SET t.col1=i.col1,t.col2=i.col2
FROM INSERTED i
INNER JOIN Yourtable t
ON t.PK = i.PK
AND t.city <> 'Miami'
IF EXISTS (SELECT 1 
           FROM INSERTED i
           INNER JOIN YourTable t
           ON t. t.PK = i.PK
           AND t.city = 'Miami'
          )
RAISERROR ('You cant update Miami based details',16,1)


END

INSERTED is internal temporary table used by trigger
PK is primary key of yourtable
col1,col2 etc are other columns in yourtable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-02-14 : 10:19:25

awesome, thanks a lot ..one more time thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 10:22:06

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources