SQL 2000; Delete Trigger

[rkbnair]

SQL 2000 Server
-------------------

I have three tables connected by a parent-child relationship :a,b &c

I have a trigger in table b with line:
SET @lng_clientid = (SELECT lng_clientid FROM deleted)

Through the Enterprises manager when I delete a record from table a, I get the following message:
"Subquery returned more than one value"

However, even though there are two records in b, it should delete one record at a time (in that way there is only one value for @lng_clientid ) and should not give that error; right?

Why is this happening so?

[rzambare]

Its a table level trigger and not row level trigger. You should count the rows in deleted table (Select Count(*) from deleted) before process the table.

[rkbnair]

Can we make a row level trigger? What is the difference between a table level and row level trigger?

[rzambare]

No, it is not possible in SQL Server 2000 or earlier. It is there with Oracle