June 25, 2003 at 9:04 am
I'm a little stumped on how to make this work... I know it's staring me in the face, but.....
I'm trying to create a trigger that fires on updates.. I've many tables that have the same fields, and these fields are non-unique and potentially repetitive numbers. The best way to fix this would be to redesign the tables, but that's not possible (I'm supposed to fix this thing).
My question is I want to update a feild called "SEV" across all my tables- So far I have this bit of code:
CREATE TRIGGER UPDATESEV_ClientBased
ON ClientBased
FOR Update as
if update (SEV)
begin
Update ClientsAccountsPayable
Set SEV = inserted.sev
from ClientsAccountsPayable, deleted, inserted
where 'ClientBased.ID' = 'ClientsAccountsPayable.ID'
end
So what I'm trying to do is update the SEV field using the ID field in each row. (i.e. when the SEV is updated on my ClientBased table, it updates the SEV on the ClientsAccountsPayable table in only the row where the ID field from ClientBased matches the ID field from ClientsAccountsPayable)
The problem is that it tells me that I'm afecting too many fields... or a couple other errors.... I'm using Access as an OLE DB tool to access SQL Server 2000, and I get more errors from Access than from direct editing, but there's still some there.
I've managed to make the update change ALL the SEV's in the ClientsAccountsPayable.... but obviously that's not what I want. (I did this by changing the last line of code to "where deleted.sev=ClientsAccountsPayable.sev"
One more thing... my bit of code above exectues fine in the Query Analyzer.. it's in the actual playing out that it screws up.
Marcus
I'm a DBA newbie- learrning on the fly. Thanks for any help.
I'm a DBA newbie- learrning on the fly. Thanks for any help.
June 25, 2003 at 9:35 am
You should include some sort of join to the inserted or deleted table instead of referring to the ClientBased table.
CREATE TRIGGER UPDATESEV_ClientBased
ON ClientBased
FOR Update AS
IF UPDATE(SEV)
BEGIN
UPDATE ClientsAccountsPayable
SET ClientsAccountsPayable.SEV = inserted.sev
FROM inserted
WHERE inserted.[ID] = ClientsAccountsPayable.[ID]
END
The idea is that the 'inserted' and 'deleted' table contains the structure of the ClientBased table. So there is no need to join to the ClientBased table.
You might need to join to the 'deleted' table if the ID field could have been updated.
June 25, 2003 at 9:38 am
I see..... thanks so much..
I was thinking that the trigger wouldn't know which table to look at..... but that doesn't make sense. Your way worked perfectly... thanks
I'm a DBA newbie- learrning on the fly. Thanks for any help.
I'm a DBA newbie- learrning on the fly. Thanks for any help.
June 25, 2003 at 12:05 pm
FYI, in SQL Server the trigger fires once, reguarless of the number of rows effected. So you may want to walk the inserted table with a cursor.
June 25, 2003 at 12:06 pm
quote:
FYI, in SQL Server the trigger fires once, reguarless of the number of rows effected. So you may want to walk the inserted table with a cursor.I doubt I'll need that on this particular trigger... it's pretty much 1-to-1 ratio, but I may need it later on.. thanks.
BTW... you do mean that the trigger will only update one reference, right?
I'm a DBA newbie- learrning on the fly. Thanks for any help.
I'm a DBA newbie- learrning on the fly. Thanks for any help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply