Update Trigger - SQL Server Forums

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

Transact-SQL (2005)

Update Trigger

Author

Topic

Rupa
Posting Yak Master

123 Posts

Posted - 2007-11-08 : 06:45:39

Hey all

I have the following trigger:

ALTER TRIGGER [CardStatusChange]
ON dbo.tbl_xData
AFTER UPDATE
AS

if update (PrintDate)
begin
UPDATE tbl_xData
SET CardStatus = 'Processed', CardStatusChanged = getdate()

end

The updates the CardStatus and CardStatusChanged columns for all the rows even if the card has not been printed. So the PrintDate is null for the rest of the rows but even if one card is printed and the PrintDate column is updated..it will update the two columns for all the rows. Am I doing something wrong??

Any response will be highly appreciated.

Thanks,
Rupa

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-08 : 06:49:19

you have to join it to the inserted pseudo table


UPDATE t1
SET CardStatus = 'Processed', CardStatusChanged = getdate()
FROM tbl_xData t1 JOIN inserted i on t1.yourId = i.yourId

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Rupa
Posting Yak Master

123 Posts

Posted - 2007-11-08 : 06:55:31

Worked like a charm :-) Thank you soooooo much spirit1 I knew it was something to do with inserted but didn't know how to work around it. Thank you again.

Rupa :-)

Rupa
Posting Yak Master

123 Posts

Posted - 2007-11-09 : 04:16:30

Hey all

Another query:

ALTER TRIGGER [CardStatusChangeToPending]
ON [dbo].[tbl_xData]
AFTER UPDATE
AS

if ((update(Forename)) OR (update(Surname)) OR (update(JobTitle)) OR (update(ExpiryDate)))
begin
UPDATE t1
SET CardStatus = 'Pending', CardStatusChanged = getdate()
FROM tbl_xData t1 JOIN inserted i on t1.StaffNo = i.StaffNo

end

I would like the CardStatus to change to 'Pending' only if the above four fields are updated. When I run the import, there is a change in PhoneNo and that gets updated but the trigger changes the status to 'Pending' when it shouldn't. Any ideas what I'm doing wrong here??

Your response will be highly appreciated.

Many thanks,

Rupa

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-09 : 04:20:35

i'd say use AND instead of OR..?

Em

Rupa
Posting Yak Master

123 Posts

Posted - 2007-11-09 : 04:24:50

Thx Em but that didn't work either...still changes it to Pending on update of PhoneNo field.

Rupa

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-09 : 04:39:19

sorry, didn't read it properly.
are there any other triggers on that table?

Em

Rupa
Posting Yak Master

123 Posts

Posted - 2007-11-09 : 04:41:23

Yes..And I'm thinking that's the problem?? Thx Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-09 : 04:43:44

most likely, perhaps something that's updating those columns?

Em

Rupa
Posting Yak Master

123 Posts

Posted - 2007-11-09 : 04:49:57

Yes!! Can I combine the two??

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-09 : 04:52:30

i generally like to keep just 1 update trigger / delete trigger etc on each table to keep it simple. you should just be able to combine the logic.
Post them here and we'll see

Em

Rupa
Posting Yak Master

123 Posts

Posted - 2007-11-09 : 04:59:09

I've got this now:

if update (PrintDate)
begin
UPDATE t1
SET CardStatus = 'Processed', CardStatusChanged = getdate()
FROM tbl_xData t1 JOIN inserted i on t1.StaffNo = i.StaffNo
end
else if update (IssueNo)
begin
UPDATE t1
SET CardStatus = 'Pending', CardStatusChanged = getdate()
FROM tbl_xData t1 JOIN inserted i on t1.StaffNo = i.StaffNo

end

I've used elseif but I know there shouldn't be 2 'ends'?? I would like some help on this please..

Many thanks,

Rupa

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-09 : 05:07:07

do you really want the ELSE? as in... check if IssueNo has been updated only if PrintDate hasn't been updated?

Em

Rupa
Posting Yak Master

123 Posts

Posted - 2007-11-09 : 05:14:06

What I'm trying to do is:

If card has been printed, change status to: 'Processed' This is triggered from PrintDate

If card is to be printed after changes to current details, change status to: 'Pending'. This is triggered from IssueNo or can be update of Surname, Forename, JobTitle or ExpiryDate.

Many thanks,

Rupa

Kristen
Test

22859 Posts

Posted - 2007-11-09 : 05:14:07

"if update (PrintDate)"

I think this feature in a trigger is generally useless. What if some of the rows have been updated in the printDate, others in the IssueNo, and some neither?


UPDATE t1
SET CardStatus = CASE WHEN COALESCE(i.PrintDate, '19000101') <> COALESCE(D.PrintDate, '19000101') 
		THEN 'Processed' 
	WHEN COALESCE(i.IssueNo, -999) <> COALESCE(D.IssueNo, -999) THEN 'Pending'
	ELSE CardStatus END, -- No change
    CardStatusChanged = CASE WHEN COALESCE(i.PrintDate, '19000101') <> COALESCE(D.PrintDate, '19000101') 
				OR COALESCE(i.IssueNo, -999) <> COALESCE(D.IssueNo, -999)
		THEN getdate() 
	ELSE CardStatusChanged END,
FROM tbl_xData t1 
	JOIN inserted i 
		on t1.StaffNo = i.StaffNo
	JOIN deleted AS D 
		ON t1.StaffNo = D.StaffNo

Kristen

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-09 : 05:27:05

and you can include the logic for name/title/etc.. into the same case statement that Kristen has written above

i.e.
when <issueno changed> or (<name changed> and <title changed> and etc...)

(too lazy to write it all it out, sorry

)

Em

Rupa
Posting Yak Master

123 Posts

Posted - 2007-11-09 : 05:28:07

Thx Kristen

I'm having error messages:
Incorrect syntax near the keyword 'FROM'

When I remove the commas after the END, it gives me the following error:

Ambiguous column name 'CardStatus'
Ambiguous column name 'CardStatusChanged'

Many thanks

Rupa

Kristen
Test

22859 Posts

Posted - 2007-11-09 : 05:32:58

Sorry, extraneous comma


	ELSE CardStatusChanged END
FROM tbl_xData t1

Kristen

Kristen
Test

22859 Posts

Posted - 2007-11-09 : 05:34:01

[code]
UPDATE t1
SET CardStatus = CASE WHEN COALESCE(i.PrintDate, '19000101') <> COALESCE(D.PrintDate, '19000101')
THEN 'Processed'
WHEN COALESCE(i.IssueNo, -999) <> COALESCE(D.IssueNo, -999) THEN 'Pending'
ELSE t1.CardStatus END, -- No change
CardStatusChanged = CASE WHEN COALESCE(i.PrintDate, '19000101') <> COALESCE(D.PrintDate, '19000101')
OR COALESCE(i.IssueNo, -999) <> COALESCE(D.IssueNo, -999)
THEN getdate()
ELSE t1.CardStatusChanged END,
FROM tbl_xData t1
JOIN inserted i
on t1.StaffNo = i.StaffNo
JOIN deleted AS D
ON t1.StaffNo = D.StaffNo
[/code]

Kristen
Test

22859 Posts

Posted - 2007-11-09 : 05:34:56

You don't need the COALESCE for any columns that are declared as NOT NULL - that would make the code a bit more readable!

Rupa
Posting Yak Master

123 Posts

Posted - 2007-11-09 : 05:38:36

I should have seen that one (embarassed)

Thank you sooooo much Kristen and Em...Much appreciated. It works!! :-)

Many thanks,

Rupa

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources