|
Posted by David Portas on 10/01/24 11:40
SUKRU wrote:
> Hello everybody.
>
> Unfortunately I am pretty new to sql-server 2000
> I need some help with a Trigger I created. I created a trigger witch
> takes the id of the affected row and does a update on a other table
> with that ID.
> The trigger works fine with one affected row. But when there are more
> then one rows affected, i get an error.
> I found out that SQL-server does not support row-level triggers.
> I should probable make my own cursor and itterate through the deleted
> table. but i don't know how to do that. since i'm new to sql-server
> 2000
>
> What I want is to itterate through the deleted table, just like the
> ORACLE FORE EACH ROW.
> retrieving the ID's and using them to update the CHECKED table.
>
> Is there anybody who has encountered the same problem and has a
> workaround for it?
>
> I would really appreciate some help with this.
>
> CREATE TRIGGER TR_Customers_CHECKED_Update ON Customers FOR UPDATE
> AS
> Begin
> DECLARE @CUSTID bigint
> SET @CUSTID = (SELECT CustomerID FROM Deleted)
> update CHECKED set approved = 'NO' where CHECKED.CustomerID = @CUSTID;
> end
Workaround for what? You don't need to do it for each row. Updates are
set based so triggers should be too. Always remember to account for
multiple row updates in troiggers. Do not use cursors in triggers.
BTW are you sure this is intended for an UPDATE trigger? The logic
looks more appropriate for a DELETE to me.
Try:
CREATE TRIGGER tr_customers_checked_update
ON customers FOR UPDATE
AS
BEGIN
UPDATE checked
SET approved = 'NO'
WHERE EXISTS
(SELECT *
FROM deleted
WHERE customerid = checked.customerid);
END
GO
(untested)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
[Back to original message]
|