Link to home
Start Free TrialLog in
Avatar of Sailing_12
Sailing_12

asked on

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

Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

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.
Avatar of Russ Suter
Russ Suter

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.
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.
Avatar of Sailing_12

ASKER

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.
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?
SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@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. :)
No that's not what I mean. The trigger will still fire. They cannot be prevented.
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.
Right, but it will fire blanks. :)
@sailing_12

Try my code.
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. :(
What do you mean? What primaries?
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'
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
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.
Russ - if 'for update' runs on both updates and deletes, what is 'for delete' for? when the entire table is deleted?
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.
Zberteoc - really? ....because it has negatively impacted the existing functionality.
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
More detail about the problem than was originally known lead to correct solution.
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.
I believe that's what I did - EE automatically assigned me 0 points, the available points were split between Zberteoc and Russ.
You post ID: 42516871 this is marked as Accepted Solution.