Trigger Question

Question

Post reply

Trigger Question

tonyeveland

SSC Rookie

Points: 49
More actions
December 15, 2008 at 10:19 am

#132985

I have:
test.deleteflag ('Y' or 'N')
test.deletedate
When someone updates test.deleteflag to 'Y', I want to set test.deletedate = GETDATE()
I tried this:
ALTER TRIGGER test_delete ON test FOR UPDATE AS
IF UPDATE (deleteflag) BEGIN
UPDATE test SET deletedate = GETDATE()
END
It updated test.deletedate for every row. No good.
So, I tried this:
ALTER TRIGGER test_delete ON test FOR UPDATE AS
IF UPDATE (deleteflag) BEGIN
UPDATE test SET deletedate = GETDATE()
WHERE deleteflag = 'Y'
END
This was better. It didn't change every row, just every row that had ever been deleted in addition to the one I wanted to change. Still no good.
How do I change just the test.deletedate in the row whose test.deleteflag was updated?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Look in Books online for the INSERTED AND DELETED tables.

These are tables that hold the information of the data that is being changed.

So you could join your table to the inserted table and that way you only updating rows that have been updated.

if you still stuck give us a shout.

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

ps. One Orange Chip Points: 29423 More actions · Answer 2

I'm assuming a column is present in the table that uniquely identifies that row. eg ID

CREATE TRIGGER trg_test on test after UPDATE

AS

UPDATE test set deletedate=GETDATE() where id in (SELECT id FROM inserted)

Pradeep Singh

ps. One Orange Chip Points: 29423 More actions · Answer 3

sry missed the condition...

where id in(select id from inserted)

AND deleteflag='Y'

Pradeep Singh

tonyeveland SSC Rookie Points: 49 More actions · Answer 4

Thanks for the response.

I tried this:

ALTER TRIGGER test_delete ON test FOR UPDATE AS

IF UPDATE (deleteflag) BEGIN

UPDATE test SET deletedate = GETDATE()

FROM test,deleted,inserted

WHERE deleted.deleteflag <> inserted.deleteflag

END

It updated every row.

So, I tried this:

ALTER TRIGGER test_delete ON test FOR UPDATE AS

IF UPDATE (deleteflag) BEGIN

UPDATE test SET deletedate = GETDATE()

FROM test

JOIN deleted on deleted.serialnum=test.serialnum

JOIN inserted on inserted.serialnum=test.serialnum

WHERE deleted.deleteflag <> inserted.deleteflag

END

It also updated every row.

Am I close?

tonyeveland SSC Rookie Points: 49 More actions · Answer 5

Pradeep,

That worked! Thank you very much.

It only changed the one row but the messages look like it did much more. Is this ok?

(16134 row(s) affected)

(1 row(s) affected)

ps. One Orange Chip Points: 29423 More actions · Answer 6

can u paste the table structure, the exact trigger, the insert statement...

Pradeep Singh

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

I think that you've got more than one trigger there.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

tonyeveland SSC Rookie Points: 49 More actions · Answer 8

Pradeep,

I appreciate your time very much. You have helped me a lot.

I don't get that result any more. I must have had something wrong. Now I get:

(1 row(s) affected)

Tony

tonyeveland SSC Rookie Points: 49 More actions · Answer 9

tonyeveland

SSC Rookie

Points: 49

December 15, 2008 at 11:37 am

#912682

R. Barry,

Thanks for your response.

Nice blog.

Tony

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Thanks for the feed back, Tony.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]