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.
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
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.
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.
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.
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.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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. :)
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.
ASKER
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.
There are no other triggers on the table.
Right, but it will fire blanks. :)
@sailing_12
Try my code.
Try my code.
ASKER
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?
ASKER
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'
ASKER
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
ASKER
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.
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.
ASKER
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.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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.
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.