Trigger After Delete - I need a Trigger Before Delete

[candide_sh@yahoo.de]

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]

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

(candide_sh@yah oo.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@sommarsk og.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

[candide_sh@yahoo.de]

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

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 <esq...@sommars kog.sewrote:

(candide...@yah oo.de) writes:

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

>

[Erland Sommarskog]

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

(candide_sh@yah oo.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/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=285655

that you can vote for if you like.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[DA Morgan]

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

Erland Sommarskog wrote:

(candide_sh@yah oo.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/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=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
damorgan@x.wash ington.edu (replace x with u to respond)

[Alex Kuznetsov]

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

On Jul 20, 5:49 pm, DA Morgan <damor...@psoug .orgwrote:

Erland Sommarskog wrote:

(candide...@yah oo.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...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
damor...@x.wash ington.edu (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]

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

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
damorgan@x.wash ington.edu (replace x with u to respond)
Puget Sound Oracle Users Group

https://www.psoug.org

[Alex Kuznetsov]

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

On Jul 21, 11:51 am, DA Morgan <damor...@psoug .orgwrote:

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
damor...@x.wash ington.edu (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]

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

Alex Kuznetsov wrote:

On Jul 21, 11:51 am, DA Morgan <damor...@psoug .orgwrote:

>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
>damor...@x.was hington.edu (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