Microsoft SQL Server forum - Trigger problem no rowlevel support, please help!

Author

Trigger problem no rowlevel support, please help!

SUKRU

2006-02-20, 1:23 pm

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

David Portas

2006-02-20, 1:23 pm

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
--

--CELKO--

2006-02-20, 8:24 pm

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

Your design sounds flawed. You have a customer_id which has a huge
size, and no way to validate it (check digit, anything??) Next, we
seldom use binary flags in SQL; instead we have a history with (start,
finish, status) triplets and/or an encodeing scheme for the status.

When I see a table named "CHECKED", I get scared. That is a status,
not an entity or relationship!! It means that you have split an
attribute out of an entity and made it into a separate table. If you
had a "MalePersonnel" and a "FemalePersonnel", you see the flaw of a
split on "sex" immediately.

Knowing that uppercase names are almost 10 times more often mis-read or
mis-typed, why did you use them? We had no choice in the days of
punchcards. In fact, you whoel design seems to be a punch card system
done in SQL.

You need to learn BASIC RDBMS, and how to use VIEWs.

SUKRU

2006-02-21, 7:23 am

Hello Celko,

Thanks for the reply!
Your right I'm a beginner, but the example above is ficticious!
In fact there is no checked table! I made that up. I choose the checked
name just for practice sake. The original tables are in Dutch, i
wouldn't want to bother you with Dutch ;).

Further more i'm not very familiar with sql-server coding conventions.
I will look it up on the internet.

the main issue is that I couldn't get the original trigger to work
because if my trigger returned more then one row i would get an error.
I've got an example in postgreSQL where the trigger works with the "for
each row function". but SQL-server 2000 doesn't support the "for each
row" statement!

But thank you for your advise, i will apply your advise the next time i
post an message!

--CELKO-- wrote:
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications.
>
> Your design sounds flawed. You have a customer_id which has a huge
> size, and no way to validate it (check digit, anything??) Next, we
> seldom use binary flags in SQL; instead we have a history with (start,
> finish, status) triplets and/or an encodeing scheme for the status.
>
> When I see a table named "CHECKED", I get scared. That is a status,
> not an entity or relationship!! It means that you have split an
> attribute out of an entity and made it into a separate table. If you
> had a "MalePersonnel" and a "FemalePersonnel", you see the flaw of a
> split on "sex" immediately.
>
> Knowing that uppercase names are almost 10 times more often mis-read or
> mis-typed, why did you use them? We had no choice in the days of
> punchcards. In fact, you whoel design seems to be a punch card system
> done in SQL.
>
> You need to learn BASIC RDBMS, and how to use VIEWs.

Erland Sommarskog

2006-02-25, 9:44 am

SUKRU (assuikersays@hotmai
l.com) writes:
> Thanks for the reply!
> Your right I'm a beginner, but the example above is ficticious!
> In fact there is no checked table! I made that up. I choose the checked
> name just for practice sake. The original tables are in Dutch, i
> wouldn't want to bother you with Dutch ;).

Heck why not? Most of Celko's posts are double-dutch anyway, so what's
the difference. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Sponsored Links