Trigger After Delete - I need a Trigger Before Delete

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

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

(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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

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 wrote:
> (candide...@yahoo.de) writes:
> > I googled around some time but I found no solution for this issue
> > (SS2000).
>

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

(candide_sh@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/SQLServer/feedback/ViewFeedbac k.aspx?FeedbackID=285655
that you can vote for if you like.


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

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

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

Erland Sommarskog wrote:
> (candide_sh@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/SQLServer/feedback/ViewFeedbac k.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.washington.edu (replace x with u to respond)

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

On Jul 20, 5:49 pm, DA Morgan wrote:
> Erland Sommarskog wrote:
> > (candide...@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/SQLServer/feedback/ViewFeedba ck.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.washington.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...

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

Alex Kuznetsov wrote:
> On Jul 20, 5:49 pm, DA Morgan wrote:
>> Erland Sommarskog wrote:
>>> (candide...@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/SQLServer/feedback/ViewFeedbac k.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.washington.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...

On the other hand you are incorrect. Oracle gives developers the choice
of firing one per row or once per statement and always has. In fact the
default is once per statement. But no none is perfect. If they were we
would all be unemployed.

Here are some samples so you can tell the difference:

CREATE OR REPLACE TRIGGER statement_level
BEFORE UPDATE
ON orders
DECLARE
vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
dbms_output.put_line(vMsg);
END statement_level;
/

CREATE OR REPLACE TRIGGER statement_level
BEFORE UPDATE
FOR EACH ROW <---- if this isn't here it is statement level
ON orders
DECLARE
vMsg VARCHAR2(30) := 'Row Level Trigger Fired';
BEGIN
dbms_output.put_line(vMsg);
END statement_level;
/
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

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

DA Morgan wrote:
> Alex Kuznetsov wrote:
>> On Jul 20, 5:49 pm, DA Morgan wrote:
>>> Erland Sommarskog wrote:
>>>> (candide...@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/SQLServer/feedback/ViewFeedbac k.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.washington.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...
>
> On the other hand you are incorrect. Oracle gives developers the choice
> of firing one per row or once per statement and always has. In fact the
> default is once per statement. But no none is perfect. If they were we
> would all be unemployed.
>
> Here are some samples so you can tell the difference:
>
> CREATE OR REPLACE TRIGGER statement_level
> BEFORE UPDATE
> ON orders
> DECLARE
> vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
> BEGIN
> dbms_output.put_line(vMsg);
> END statement_level;
> /
>
> CREATE OR REPLACE TRIGGER row_level
> BEFORE UPDATE
> FOR EACH ROW <---- if this isn't here it is statement level
> ON orders
> DECLARE
> vMsg VARCHAR2(30) := 'Row Level Trigger Fired';
> BEGIN
> dbms_output.put_line(vMsg);
> END row_level;
> /

Forgot to rename the second one ... it is row-level so I've altered
it above.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

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

DA Morgan wrote:
> Alex Kuznetsov wrote:
>> On Jul 20, 5:49 pm, DA Morgan wrote:
>>> Erland Sommarskog wrote:
>>>> (candide...@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/SQLServer/feedback/ViewFeedbac k.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.washington.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...
>
> On the other hand you are incorrect. Oracle gives developers the choice
> of firing one per row or once per statement and always has. In fact the
> default is once per statement. But no none is perfect. If they were we
> would all be unemployed.
>
> Here are some samples so you can tell the difference:
>
> CREATE OR REPLACE TRIGGER statement_level
> BEFORE UPDATE
> ON orders
> DECLARE
> vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
> BEGIN
> dbms_output.put_line(vMsg);
> END statement_level;
> /
>
> CREATE OR REPLACE TRIGGER statement_level
> BEFORE UPDATE
> FOR EACH ROW <---- if this isn't here it is statement level
> ON orders
> DECLARE
> vMsg VARCHAR2(30) := 'Row Level Trigger Fired';
> BEGIN
> dbms_output.put_line(vMsg);
> END statement_level;
> /
I beg to differ. A trigger that fires only for the first row it runs
into is NOT a statement trigger.
Statement triggers allow access to the NEW TABLE (aka INSERTED) and OLD
TABLE (DELETED). Also a statement trigger fires even if no row is
modified. I'm unsure that is the case in Oracle.

Does Oracle still have issues with "mutating table conflicts" or is that
finally fixed in 11g?

I'd be careful with throwing stones in the glass house.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

Serge Rielau wrote:
> DA Morgan wrote:
>> Alex Kuznetsov wrote:
>>> On Jul 20, 5:49 pm, DA Morgan wrote:
>>>> Erland Sommarskog wrote:
>>>>> (candide...@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/SQLServer/feedback/ViewFeedbac k.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.washington.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...
>>
>> On the other hand you are incorrect. Oracle gives developers the choice
>> of firing one per row or once per statement and always has. In fact the
>> default is once per statement. But no none is perfect. If they were we
>> would all be unemployed.
>>
>> Here are some samples so you can tell the difference:
>>
>> CREATE OR REPLACE TRIGGER statement_level
>> BEFORE UPDATE
>> ON orders
>> DECLARE
>> vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
>> BEGIN
>> dbms_output.put_line(vMsg);
>> END statement_level;
>> /
>>
>> CREATE OR REPLACE TRIGGER statement_level
>> BEFORE UPDATE
>> FOR EACH ROW <---- if this isn't here it is statement level
>> ON orders
>> DECLARE
>> vMsg VARCHAR2(30) := 'Row Level Trigger Fired';
>> BEGIN
>> dbms_output.put_line(vMsg);
>> END statement_level;
>> /
> I beg to differ. A trigger that fires only for the first row it runs
> into is NOT a statement trigger.
> Statement triggers allow access to the NEW TABLE (aka INSERTED) and OLD
> TABLE (DELETED). Also a statement trigger fires even if no row is
> modified. I'm unsure that is the case in Oracle.
>
> Does Oracle still have issues with "mutating table conflicts" or is that
> finally fixed in 11g?
>
> I'd be careful with throwing stones in the glass house.
>
> Cheers
> Serge

No stones ... just correcting an incorrect statement. Can't talk about
11g ... you'll have to wait just like everybody else. Then you can try
to flatter Mark through imitation.

However I am fascinated by your technical description of a statement
level trigger. How does a trigger "run into" a row? Is it more likely
to happen if its been out drinking?

And perhaps I can familiarize you with the documentation on the product
you work on for IBM.
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index .jsp?topic=/com.ibm.db2.doc.sqlref/rctrg.htm

Take a good look at table 76: Allowable combinations of attributes in a
trigger definition.

OLD and NEW are only allowed for ROW level triggers in DB2 not STATEMENT
level. It appears that you are confusing NEW and OLD with NEW_TABLE AS
and OLD_TABLE AS.

Also see:

Notes:
1. The same clause must not be specified more than once. OLD TABLE
and NEW TABLE must be specified only for AFTER triggers.
2. FOR EACH STATEMENT must not be specified for BEFORE triggers.

Statement #2 conflicts with Table 76 which clearly shows FOR EACH
STATEMENT and BEFORE.

But heck you finally built Instead Of Triggers into 9.1 for z/OS. That's
progress
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

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

DA Morgan wrote:
> Serge Rielau wrote:
>> DA Morgan wrote:
>>> Alex Kuznetsov wrote:
>>>> On Jul 20, 5:49 pm, DA Morgan wrote:
>>>>> Erland Sommarskog wrote:
>>>>>> (candide...@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/SQLServer/feedback/ViewFeedbac k.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.washington.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...
>>>
>>> On the other hand you are incorrect. Oracle gives developers the choice
>>> of firing one per row or once per statement and always has. In fact the
>>> default is once per statement. But no none is perfect. If they were we
>>> would all be unemployed.
>>>
>>> Here are some samples so you can tell the difference:
>>>
>>> CREATE OR REPLACE TRIGGER statement_level
>>> BEFORE UPDATE
>>> ON orders
>>> DECLARE
>>> vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
>>> BEGIN
>>> dbms_output.put_line(vMsg);
>>> END statement_level;
>>> /
>>>
>>> CREATE OR REPLACE TRIGGER statement_level
>>> BEFORE UPDATE
>>> FOR EACH ROW <---- if this isn't here it is statement level
>>> ON orders
>>> DECLARE
>>> vMsg VARCHAR2(30) := 'Row Level Trigger Fired';
>>> BEGIN
>>> dbms_output.put_line(vMsg);
>>> END statement_level;
>>> /
>> I beg to differ. A trigger that fires only for the first row it runs
>> into is NOT a statement trigger.
>> Statement triggers allow access to the NEW TABLE (aka INSERTED) and
>> OLD TABLE (DELETED). Also a statement trigger fires even if no row is
>> modified. I'm unsure that is the case in Oracle.
>>
>> Does Oracle still have issues with "mutating table conflicts" or is
>> that finally fixed in 11g?
>>
>> I'd be careful with throwing stones in the glass house.
> And perhaps I can familiarize you with the documentation on the product
> you work on for IBM.
> http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index .jsp?topic=/com.ibm.db2.doc.sqlref/rctrg.htm
Daniel,

Please check the newsgroup. This is a MS SQL Server group, not DB2.
Please remain stay on topic. I'll gladly answer to your DB2 questions in
the appropriate forum.

But since you have identified OLD TABLE and NEW TABLE, perhaps you can
bless us with the syntax for an Orcale statement trigger that uses them
(or their equivalents, let's not get hung up on syntax after all)
Perhaps I just can't find :old_table and :new_table in the books.

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.

If an Oracle statement trigger can access OLD ROW and NEW ROW and
obviously fire only once than I do not have a better word than "running
into a row" because by definition that row would be randomly selected
from the intermediate resultset for e.g.
INSERT INTO T SELECT * FROM S.
SQL being set oriented and all...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

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

On Jul 21, 11:51 am, DA Morgan wrote:
> 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.washington.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/

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

Alex Kuznetsov wrote:
> On Jul 21, 11:51 am, DA Morgan wrote:
>> 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.washington.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