Trigger After Delete - I would need a Trigger Before Delete

[candide_sh]

hello,
I googled around some time but I found no solution for this issue
(SS2000).

I have a table tblB which has to be filled whenever in another table
tblA records are inserted, updated or deleted.
So I created one trigger which works fine for inserts and updates and
fills my tblB. tblB is filled with other fields which I get from a
view vwC.

The issue is about this view. When in tblA a record is deleted, the
corresponding record in vwC does not exist and I can't fill tblB. I
tried around with INSTEAD OF -Trigger and got error message because
tblA has RI cascades so this is not possible. A temp table could be
the right way? Can you show me an example?

thanks
--
candide_sh

[Roy Harvey]

I believe you will have to do it without the view. You would need the
view to join the DELETED table to those other tables, and no view can
reference DELETED. In the trigger, in the FROM clause, replace the
reference to the view with a derived table using the same SELECT as
the view. So instead of:

FROM ViewName

you will need

FROM (<SELECT.....>) as ViewName

Note that if you have an alias assigned to the view reference that
would go after the "as" rather than ViewName.

Roy Harvey
Beacon Falls, CT

On Tue, 17 Jul 2007 08:04:59 -0700, ca********@yahoo.de wrote:

>hello,
I googled around some time but I found no solution for this issue
(SS2000).

I have a table tblB which has to be filled whenever in another table
tblA records are inserted, updated or deleted.
So I created one trigger which works fine for inserts and updates and
fills my tblB. tblB is filled with other fields which I get from a
view vwC.

The issue is about this view. When in tblA a record is deleted, the
corresponding record in vwC does not exist and I can't fill tblB. I
tried around with INSTEAD OF -Trigger and got error message because
tblA has RI cascades so this is not possible. A temp table could be
the right way? Can you show me an example?

thanks