Can you perform 3 actions in one trigger?

Question

Can you perform 3 actions in one trigger?

Viewing 14 posts - 31 through 43 (of 43 total)

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

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 1

That's why in this case I cant do set base, because I need to use data from other tables as well, not just the inserted data. Well thanks for all the help everyone. You guys have really helped me I appreciate it.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

It'll still work. You can join the Inserted table to the other tables to get that data.

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

crazy_new (9/2/2014)
That's why in this case I cant do set base, because I need to use data from other tables as well, not just the inserted data.

Why not?

That's like saying that you can't make Customers and Orders separate tables because you need to use their data together.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eirikur Eiriksson SSC Guru Points: 182901 More actions · Answer 4

crazy_new (9/2/2014)
Just a quick question. In the inserted/deleted tables, does it contain all of the records of the last single transaction made? And if I use set based logic, how would I put the name ID in a variable then?

Just a quick question, did you look at the code sample I posted earlier?

😎

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 5

Just a quick scan, you have an update table, which is the same table I am deleting and inserting to, I cant update because there are no unique identifiers. But I found a way to implement set based logic in my triggers. It all just started making sense like a light bulb going on hehe. I can post it if you would like to see?

Eirikur Eiriksson SSC Guru Points: 182901 More actions · Answer 6

crazy_new (9/3/2014)
Just a quick scan, you have an update table, which is the same table I am deleting and inserting to, I cant update because there are no unique identifiers. But I found a way to implement set based logic in my triggers. It all just started making sense like a light bulb going on hehe. I can post it if you would like to see?

Good stuff, now I feel we are getting somewhere;-)

Please post the code, I'm interested.

😎

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 7

DECLARE @NameID int, @RoleID int, @LoginID char(20)

SET @NameID = (

SELECT

NAME_ID

FROM

inserted

)

SET @RoleID = (

SELECT

NAME_FIELD_NUMBER

FROM

inserted

)

SET @LoginID = (

SELECT

STRING_DATA

FROM

inserted

)

DELETE FROM PAT_ACCOUNT_MANAGER

WHERE NAME_ID = @NameID

AND ROLE_TYPE_ID = @RoleID

AND LOGIN_ID <> @LoginID

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

That's good, but it's still going to fail if you insert more than one row at a time. Better to join PAT_ACCOUNT_MANAGER to the Inserted table to do the deletes, rather than attempt to get the IDs into a scalar variable.

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Still won't handle multiple row inserts.

Any time you fetch values into a variable, your trigger won't be able to handle multiple rows. You need to get rid of all those SET statements and write the trigger with no variables in it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 10

How would I have to change it to handle multiple records then?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

John's already answered that

John Mitchell-245523 (9/3/2014)
Better to join PAT_ACCOUNT_MANAGER to the Inserted table to do the deletes, rather than attempt to get the IDs into a scalar variable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 12

So it will look something like this then?

DELETE pat FROM PAT_ACCOUNT_MANAGER pat

JOIN

INSERTED I ON I.PAT_account_manager_ID = pat.PAT_ACCOUNT_MANAGER_ID

WHERE pat.NAME_ID = i.NAME_ID

AND pat.ROLE_TYPE_ID = i.ROLE_TYPE_ID

AND pat.LOGIN_ID <> i.LOGIN_ID

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 13

Yes! That certainly looks right. Obviously you'll want to test it and make sure it gives correct results in all circumstances, including multiple inserts.

Well done - you've listened to advice and ended up with something much more compact and robust than you started with.

John

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 14

Well thank you guys for all your help. I have learned a lot and I couldn't have done it with out you guys.