Trigger After Delete - I 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 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?

thanks
--
candide_sh

[Erland Sommarskog]

(ca********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[candide_sh]

Hello,

I met a SS-Professional yesterday and he told me to use stored
procedures. As there was no time to waste I did so and it seems to
work.
Still wondering there's no Before-Trigger event in SS2005. maybe in
SS2008?

thanks Erland for your hints

On 18 Jul., 10:38, Erland Sommarskog <[e-mail]:

([e-mail]) writes:

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

[Erland Sommarskog]

(ca********@yahoo.de) writes:

I met a SS-Professional yesterday and he told me to use stored
procedures. As there was no time to waste I did so and it seems to
work.
Still wondering there's no Before-Trigger event in SS2005. maybe in
SS2008?

I have not heard anything on that.

I fond a request for BEFORE TRIGGERS on
https://connect.microsoft.com/SQLSer...dbackID=285655
that you can vote for if you like.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[DA Morgan]

Erland Sommarskog wrote:

(ca********@yahoo.de) writes:

>I met a SS-Professional yesterday and he told me to use stored
procedures. As there was no time to waste I did so and it seems to
work.
Still wondering there's no Before-Trigger event in SS2005. maybe in
SS2008?

I have not heard anything on that.

I fond a request for BEFORE TRIGGERS on
https://connect.microsoft.com/SQLSer...dbackID=285655
that you can vote for if you like.

SQL Server is now the only major commercial database without them.

Given how easy they would be to implement, Oracle had them in 1989,
does anyone know why the delay?
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu (replace x with u to respond)

[Alex Kuznetsov]

On Jul 20, 5:49 pm, DA Morgan <[e-mail]:

Erland Sommarskog wrote:

([e-mail]) writes:

I met a SS-Professional yesterday and he told me to use stored
procedures. As there was no time to waste I did so and it seems to
work.
Still wondering there's no Before-Trigger event in SS2005. maybe in
SS2008?

I have not heard anything on that.

I fond a request for BEFORE TRIGGERS on
https://connect.microsoft.com/SQLSer...edback.aspx?Fe...
that you can vote for if you like.

SQL Server is now the only major commercial database without them.

Given how easy they would be to implement, Oracle had them in 1989,
does anyone know why the delay?
--
Daniel A. Morgan
University of Washington
[e-mail] (replace x with u to respond)

Agreed, but on the other hand Oracle fires triggers once per row, not
once per statement - and that can really drag performance. No RDBMS is
perfect...

[DA Morgan]

Serge Rielau wrote:

To the best of my knowledge there is no such thing as OLD TABLE/INSERTED
and NEW TABLE/DELETED in Oracle, hence there are no statement triggers
in Oracle which actually can operate on the data set.
And that was Alex's point.

No it wasn't: That is your point and if you wish to make it move it to
c.d.o.s. Sorry folks.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Alex Kuznetsov]

On Jul 21, 11:51 am, DA Morgan <[e-mail]:

Serge Rielau wrote:

To the best of my knowledge there is no such thing as OLD TABLE/INSERTED
and NEW TABLE/DELETED in Oracle, hence there are no statement triggers
in Oracle which actually can operate on the data set.
And that was Alex's point.

No it wasn't: That is your point and if you wish to make it move it to
c.d.o.s. Sorry folks.
--
Daniel A. Morgan
University of Washington
[e-mail] (replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org

Actually Serge got it right, that was exactly my point. To my best
knowledge one year ago, in10G, Oracle's triggers could not access the
whole set of modified rows. Nor the body of the trigger would fire if
no rows were modified at all. This does not qualify as a statement
level trigger. Pls correct me if I am wrong.

Cheers,
Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/

[Serge Rielau]

Alex Kuznetsov wrote:

On Jul 21, 11:51 am, DA Morgan <[e-mail]:

>Serge Rielau wrote:

>>To the best of my knowledge there is no such thing as OLD TABLE/INSERTED
and NEW TABLE/DELETED in Oracle, hence there are no statement triggers
in Oracle which actually can operate on the data set.
And that was Alex's point.

No it wasn't: That is your point and if you wish to make it move it to
c.d.o.s. Sorry folks.
--
Daniel A. Morgan
University of Washington
[e-mail] (replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org

Actually Serge got it right

Thanks, and Daniel: It is YOU you added Oracle into the discussion:
"Given how easy they would be to implement, Oracle had them in 1989,
does anyone know why the delay?"
So if you're not willing to see things through don't bother posting

If you start a flame expect to get burned.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab