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

(candide_sh (AT) yahoo (DOT) de) writes:
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 (AT) sommarskog (DOT) 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

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... (AT) sommarskog (DOT) se> wrote:

(candide_sh (AT) yahoo (DOT) de) writes:
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, esquel (AT) sommarskog (DOT) 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

Erland Sommarskog wrote:
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)

On Jul 20, 5:49 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:
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...

Alex Kuznetsov wrote:
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)

DA Morgan wrote:
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

DA Morgan wrote:
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

Serge Rielau wrote:
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. <g>

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/infoce...lref/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 <g>
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)