.... or a "deleted" reference in a INSERT trigger.
I have two near identical dbs, on the same SQLA 9 server.
The trigger belows works fine on db 1 for INSERT, UPDATE and
DELETE; however on db 2 the DELETE scenario results in
error: "Table 'inserted' not found" There must be a db
option involved, but what is it?
Joel

CREATE TRIGGER HistBudget_ChgMgr AFTER INSERT, UPDATE,
DELETE
ON DBA.Budget
REFERENCING NEW as inserted OLD AS deleted
FOR EACH STATEMENT
WHEN (EXISTS (select 1 from inserted where inserted.DataFrom
= 'Manual')
OR EXISTS (select 1 from deleted where deleted.DataFrom =
'Manual'))
-- 11/20/07 JSC Adapted from CPLA format
BEGIN
declare EventIDv integer ;
IF INSERTING THEN
SET EventCodeIDV = 31 ; --,32,33
ELSEIF UPDATING THEN
SET EventCodeIDV = 35 ; --,36,37
ELSEIF DELETING THEN
SET EventCodeIDV = 39; -- ,40,41
END IF;
INSERT INTO EVENT
(EventCodeID,Description,PreviousEventID) select
EventCodeIDV,EC.Name,NULL
from EventCode EC where EC.EventCodeID = EventCodeIDV;
select @@identity into EventIDv ;
IF INSERTING OR UPDATING THEN
INSERT into HistBudget ( "EventID" ,
"ChangeID" ,
"Work_order" ,
"Contractor" ..... (abbr to keep size down)
)
Select EventIDv ,
"ChangeID" ,
"Work_order" ,
"Contractor" .....
from inserted where inserted.DataFrom = 'Manual';
ELSE
INSERT into HistBudget ( "EventID" ,
"ChangeID" ,
"Work_order" ,
"Contractor" .....
)
Select EventIDv ,
"ChangeID" ,
"Work_order" ,
"Contractor" .....
from deleted where deleted.DataFrom = 'Manual';
END IF ;
END ;

Hmmm ... a when-clause in a statement trigger ... you working case is
probably just
ignoring that ... but the docs clearly state

WHEN clause The trigger fires only for rows where the
search-condition
evaluates to true. The WHEN clause can be used only with ***row level
triggers***.

your's is clearly not a row-level trigger ... and you seem to have fallen
into a dark corner ...

I would try removing it ... and then consider an alternate implementation.



<JSCARLSON> wrote

correction:
... youR working case ....


Also removing that when-clause is basically
just removing a noop since your insert from
selects complete the picture and prevent any
extraneous cases from being picked up
when the when-clause has no impact.

My take on this is simple: INSERTED is simply
not defined in the DELETE case ... as would
DELETED be undefined in the INSERT case.
And neither may have any definition within the
when-clause .....

This latter point seems to be confirmed by my tests
with row-level trigger.

"Nick Elson" <@@@nick@@@.@@@elson@sybase@@@.@@@com@@@> wrote

In my book the trigger is not behaving correctly on either
database. You seemed to have coded to a parser bug
and are getting 2 different behaviours from (otherwise)
illegal SQL.

In the case you think is working, it is only doing so because
the server is ignoring your illegal SQL. In the case that you
think it is incorrectly failing, it is only doing so because it
is actually executing your illegal SQL (see bottom paragraph).

"Why it is doing either?" and "Why there is the difference
in the two databases?" are both pretty philosophical
questions given we are no longer on the Map (as far as
legal SQL is concerned) and operating in Tera Incognito
and where my map builders say 'There be monsters here!'.

I or engineering would need to see actual databases and
exact details of what cases cause the failure.

By the way, if that clause is being respected (as mentioned
earlier from my tests where the when-clause is legal in the
row level case ) you should be getting the error you are getting
in the second database (verified here) ... so getting a fix for
this 'bug' will cause both cases to fail.

Why should it fail? Well here's my understanding ... it is a
simple matter of scope of definition. While those virtual
tables are defined within the scope of the trigger body the
are not defiined within the scope of this 'outer' when-clause.

Beyound that one can only guess what is actually happening
in your case, since I can only reproduce one of your
2 behaviours and that happens to be the one the I believe
is behaving incorrectly; not the one you found objectionable.




<JSCARLSON> wrote