SQL trigger on row update only

Hello - trying to create a SQL trigger that runs only on row updates, not row deletes. Below is my trigger code, but this is running on both and I'm not having success getting it to only run on actual updates only.

Thanks.

ALTER trigger [dbo].[clearPrimaryLocations] on [dbo].[ProviderLocations]
for update
as
begin
	if update(isPrimary)
	begin
		declare @updatedRow int, @providerId int
		set @updatedRow = (select providerlocationid from deleted)
		set @providerId = (select providerid from deleted)

		update ProviderLocations
		set isPrimary = 0
		where providerId = @providerId and providerLocationId <> @updatedRow
	End
end

Open in new window

Sailing_12PirateAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware DeveloperCommented:
There isn't a trigger for delete showing up in SSMS?

Or are you mistaking the meanings of the updated and deleted tables within a trigger? An update means you have old and new values, The deleted table holds old values, the updated table holds new values. You're only triggered on updates.

This article shows a little overview when you can expect which trigger table to exist for triggers of the types INSERT, UPDATE, and DELETE.

Bye, Olaf.
0
Russ SuterCommented:
You'll need to look at the INSERTED and DELETED pseudo tables for rows that exist in DELETED but not INSERTED and don't act on rows in the DELETED table that don't have matched rows in the INSERTED table.
0
Olaf DoschkeSoftware DeveloperCommented:
Besides, you're cascading triggers, when you update the same table, even though you check update(isPrimary), you don't want to react to updates going from 1 to 0 to cascade further updates, you only want to act on the one record getting set 1 to cause other similar rows to be set 0, so you better check for deleted.isprimary=0 AND inserted.isprimary=1, and only then cause other rows to be updated to 0.

And you overlook an update might cause multiple records to update, you act as if only a single record is updated per trigger call. A typical trigger code will join inserted and deleted tables and then more you want to act on.

Bye, Olaf.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Sailing_12PirateAuthor Commented:
No, I am not asking the meanings of the updated and deleted pseudo-tables. I am asking why the trigger is running when a row is deleted in the [dbo].[ProviderLocations] table when I have specified 'for update' in the trigger definition.

I only want this trigger to run when there is an UPDATE to an existing row in the [dbo].[ProviderLocations] table, not when a row is deleted.
0
Sailing_12PirateAuthor Commented:
That is a fair point on the cascading, though this does not seem to be happening - if it were wouldn't I be ending up in an endless loop of triggers, and my primary = 1 row would be getting set to 0 when one of the non-primary rows is updated?
0
ZberteocCommented:
Use this:
ALTER trigger [dbo].[clearPrimaryLocations] on [dbo].[ProviderLocations]
for update
as
begin
	if exists (select top 1 * from inserted)
		return

	if update(isPrimary)
	begin
		declare @updatedRow int, @providerId int
		set @updatedRow = (select providerlocationid from deleted)
		set @providerId = (select providerid from deleted)

		update ProviderLocations
		set isPrimary = 0
		where providerId = @providerId and providerLocationId <> @updatedRow
	End
end

Open in new window

0
Olaf DoschkeSoftware DeveloperCommented:
OK, fine, then you're already better informed, bit the code doesn't reflect that, it's programmed for the case these pseudo tables only contian one row.

An you then seem not to have looked into what triggers are defined for the table. The same routine can run for deletion, if there is a previous definition saying so.

Bye, Olaf.
0
Russ SuterCommented:
I only want this trigger to run when there is an UPDATE to an existing row in the [dbo].[ProviderLocations] table, not when a row is deleted.
A "for update" trigger will fire whenever an update is made to the table data. The update in this case refers to the table, not a row within the table. The only way to have the trigger differentiate between a row update and a row delete is to look at the pseudo tables. You cannot prevent the update trigger from firing when a DELETE statement is executed. All you can do is add logic to your trigger that prevents code execution when you have rows in the DELETE pseudo table that don't match rows in the INSERT pseudo table.
0
ZberteocCommented:
@Russ Suter

What you mean is that the only way to prevent from firing on DELETE is with code and that is exactly what I did in my post. :)
0
Russ SuterCommented:
No that's not what I mean. The trigger will still fire. They cannot be prevented.
0
Sailing_12PirateAuthor Commented:
Yes, the assumption here is that only one row will be present in the pseudo-tables... the front-end application allows a user to set the primary row, then the trigger will reset all of the other rows to 0 for that provider. There can only be one chosen as primary.

There are no other triggers on the table.
0
ZberteocCommented:
Right, but it will fire blanks. :)
0
ZberteocCommented:
@sailing_12

Try my code.
0
Sailing_12PirateAuthor Commented:
Zberteoc - thanks for the code, but it is not working quite right... the initial primary is not getting cleared so over time the result is multiple primaries. :(
0
ZberteocCommented:
What do you mean? What primaries?
0
Sailing_12PirateAuthor Commented:
So what I am getting from Russ here is that the trigger will always fire on update and delete - this seems consistent with my testing though seems to contradict the 'for update' in the trigger since SQL also offers 'for delete'
0
Sailing_12PirateAuthor Commented:
the isprimary = 1 is not being set to 0 for all of the other location rows for the same provider which is the goal here
0
Sailing_12PirateAuthor Commented:
so let me clarify the goal here - this is a bridge table of locations for providers. One given provider can only have one primary location, indicated by the isprimary = 1.

when an update is executed to set isprimary = 1 by the front-end app, the trigger should locate the other location rows for the same provider via the providerid column, and set all of those to 0 thus enforcing the one primary only business rule.
0
Sailing_12PirateAuthor Commented:
Russ - if 'for update' runs on both updates and deletes, what is 'for delete' for? when the entire table is deleted?
0
ZberteocCommented:
Yes, so? Why do you say it doesn't work? You wanted to prevent the code from trigger to execute on delete and that is exactly what it does.
0
Sailing_12PirateAuthor Commented:
Zberteoc - really? ....because it has negatively impacted the existing functionality.
0
ZberteocCommented:
Really what? How should I know about the impact in your process? I simply answered specifically to your question. If there is more to the problem than you should describe it properly and give complete info otherwise it will be impossible to answer.
0
Sailing_12PirateAuthor Commented:
ok, I figured out what was going on here... the app is actually not executing a SQL DELETE, instead it is updating the isActive flag to 0 via a SQL UPDATE. When doing so, entity framework is passing in the entire row of values so even though the isPrimary value is not being changed, SQL is seeing it as an update and still running the trigger code (because 'if update(isPrimary)' evals to true) resulting in all other rows for that provider (including the true primary) to be set to 0.

I'm including my updated trigger which is testing successfully now.

Zberteoc - I haven't re-tested your proposed solution, but it looks like it should probably work given an actual delete.

Russ - Before discovering the above oversight, I ran SQL debug on my trigger with both a manually executed delete and update, and the debugger skipped over the trigger entirely on deletes, so I'm not so sure that the trigger fires on both as you stated - it didn't look like it to me.

Thanks for getting me to look at this from a different angle.
ALTER trigger [dbo].[clearPrimaryLocations] on [dbo].[ProviderLocations]
for update
as
begin
	if (select isprimary from inserted) = 1
	begin
		declare @updatedRow int, @providerId int
		set @updatedRow = (select providerlocationid from inserted)
		set @providerId = (select providerid from inserted)

		update ProviderLocations
		set isPrimary = 0
		where providerId = @providerId and providerLocationId <> @updatedRow and isActive = 1
	End
end

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sailing_12PirateAuthor Commented:
More detail about the problem than was originally known lead to correct solution.
0
ZberteocCommented:
As a general rule, you don't have to give yourself points when you close a question. Rather split them between the people spent considerable time to help you. Even if you figured out the complete solution by yourself without their help it would have not been possible, probably.
0
Sailing_12PirateAuthor Commented:
I believe that's what I did - EE automatically assigned me 0 points, the available points were split between Zberteoc and Russ.
0
ZberteocCommented:
You post ID: 42516871 this is marked as Accepted Solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.