Trigger After Delete - I need a Trigger Before Delete

#11

DA Morgan wrote:

Quote:

Serge Rielau wrote:
DA Morgan wrote:
Alex Kuznetsov wrote:
On Jul 20, 5:49 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:
Erland Sommarskog wrote:
(candide... (AT) yahoo (DOT) 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.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/infoce...lref/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

ld_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

#12

Serge Rielau wrote:

Quote:

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

#13

On Jul 21, 11:51 am, DA Morgan <damor... (AT) psoug (DOT) org> wrote:

Quote:

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/

#14

Alex Kuznetsov wrote:

Quote:

On Jul 21, 11:51 am, DA Morgan <damor... (AT) psoug (DOT) org> 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Trigger After Delete - I need a Trigger Before Delete

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: Trigger After Delete - I need a Trigger Before Delete - 07-21-2007 , 08:36 AM

Re: Trigger After Delete - I need a Trigger Before Delete - 07-21-2007 , 11:51 AM

Re: Trigger After Delete - I need a Trigger Before Delete - 07-21-2007 , 01:20 PM

Re: Trigger After Delete - I need a Trigger Before Delete - 07-21-2007 , 10:59 PM