Re: Trigger After Delete - I need a Trigger Before Delete

(candide_sh@xxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Update all records in VBA
    ... there's still some unique ID that is consistant in tblA and tblB after the ... are then stored in tblParts. ... doing the import and update tblA and tblB in VBA. ...
    (microsoft.public.access.modulesdaovba)
  • RE: SQL question - compare 2 tables
    ... To find which entries are in tblA but not in tblB, ... > of the parts in tblB may be missing. ...
    (microsoft.public.access.queries)
  • Strange behavior on query - Help??
    ... I have tblA with unique PrimaryKeyX. ... Then tblB has many PrimaryKeyX matches, and for each there is a fieldY ...
    (microsoft.public.access.queries)
  • Re: Option Group
    ... Yes they have the same feilds and since thay have the ... same feilds on bother tblA anf tblB I have decided to may ... Option Group: ...
    (microsoft.public.access.forms)
  • Trigger After Delete - I need a Trigger Before Delete
    ... I have a table tblB which has to be filled whenever in table tblA ... This view vwC is based on a key field used in tblA. ...
    (comp.databases.ms-sqlserver)