Posted by Erland Sommarskog on 07/18/07 08:38

(candide_sh@yahoo.de) writes:
> 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 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. This view vwC is based on a key field used in tblA.
>
> 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?

This sounds very familiar to something that I saw Roy Harvey answer to
yesterday (in a different newsgroup?). Since Roy is very usually right, I
had no reason to object to his reply, even if it wasn't what you are
looking for.

But maybe there is a solution if you are lucky. Or maybe there is not.
I would suggest that you post:

o CREATE TABLE statement(s) for you table and view.
o INSERT statements with sample data.
o The desired result given the sample.

If possible, try to reduce the table and view so that only the parts
that are relevant to the problem remains. See the tables and that,
helps to understand the problem.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]