Triggers: Getting the variable that was GOING to be sent (INSTEAD OF) and what is already there

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 2000 Forums

SQL Server Development (2000)

Triggers: Getting the variable that was GOING to be sent (INSTEAD OF) and what is already there

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-01 : 07:56:14

Brian writes "Okay, I'm trying to write a trigger so that when there is an update to my LAST_MESSAGE table, specifically when ACTION is changed, i want to see if ACTION (BEFORE they send that UPDATE), is the same value that it is trying to update. So say ACTION's value were DEPART, and the database was trying to update ACTION to make it say DEPART, i want to say "don't do that" basically. I thought i would write a trigger that would use the INSTEAD OF operator and basically govern what is to happen.

I am having trouble trying to discern these two very similar messages. Is there a way to retrieve the value of what the UPDATE was going to send to ACTION and retrieve the value of what is ALREADY IN the ACTION field or do I need to find a new idea?

Any help would be greatly appreciated.
Brian"

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-07-01 : 08:38:20

Look up "Using the inserted and deleted Tables" in BOL.

CREATE TRIGGER tr_tblA_au
ON tblA FOR UPDATE
AS
DECLARE @PresentValue AS VARCHAR(255)
DECLARE @NewValue AS VARCHAR(255)

SELECT @PresentValue = Value_vc FROM deleted

SELECT @NewValue = Value_vc FROM inserted

IF @NewValue <> @PresentValue
BEGIN
...

brianlitt
Starting Member

8 Posts

Posted - 2002-07-03 : 13:12:06

How can i do this if i want to be able to stop the INSERT/UPDATE from occuring originally. There is a trigger that updates this table, so i'm writing a trigger for this table that says "if this data is already in the table, don't do anything, but if it isn't then you can go ahead and update/insert the data". i thought that inserted and deleted tables were for if I had a trigger that was working AFTER the update had gone through. Any help would be greatly appreciated.

Thanks,
Brian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources