Before insert trigger but updating

[dbalearner]

I need to write a trigger code with the following condition

Trigger Firing Condition: Before, Insert

Does the following make sense?

create or replace trigger bond__insert_trigger
before insert
on bond
for each row
begin
set :new.confirmed = 'Y';

set :new.complete = 'Y';

set :new.reconciled = 'Y' where ( select bon.book from :new, bond where :new.bond = bond.bond ) = 99 ;
end;

 

[eagertotry]

First, you don't need "set" to assign values to variables.

Next, trigger works on each row at a time. I didn't quite get what you're trying to do in:

set :new.reconciled = 'Y' where  ( select bon.book from :new, bond where :new.bond = bond.bond ) = 99 ;

Does the bond table also has a column "bond"? In plain English, what do you want?

Something like this? - If column book of the updated row is 99, set :new.reconciled = 'Y'

If so, this should do:

begin
    :new.confirmed = 'Y';
    
    :new.complete  = 'Y';
    
    IF :new.book = 99 THEN
    :new.reconciled = 'Y';
end;

 

[dbalearner]

Thanks fore the comments. Just a minor query. In your code:

begin
:new.confirmed = 'Y';

:new.complete = 'Y';

IF :new.book = 99 THEN
:new.reconciled = 'Y';
end;

I believe there is an "END IF" missing after the following if I am correct? Thanks

IF :new.book = 99
THEN
:new.reconciled = 'Y';
END IF;

 

[sem]

Second, to assign (not compare) you should use := rather than =

:new.reconciled [b]:=[/b] 'Y';

Regards, Dima

http://seminihin.narod.ru

 

[dbalearner]

Thanks guys. After talking to the developers, I think we are getting closer to the following solution:

create or replace trigger bond_trade_ins_trigger
before insert
on bond_trade
for each row
declare
v_NumRows integer := null;
begin
-- This will stop at first match rather than trying to count them all
select count(*) into v_NumRows from dual
where exists ( select null
from
bond.book b,
bond.bond o,
:new t
where b.book = o.book
and t.bond = o.bond
and b.book = 99);
if v_NumRows > 0
then
:new.reconciled := 'Y';
end if;
end;

The question that I would like to ask is whether one can refer to the newly inserted records as :new in the select statement above?

 

[dbalearner]

When I am compiling this trigger code I get the following error:

create or replace trigger bond_trade_complete_trade
before insert
on bond_trade
for each row
declare
v_NumRows integer := null;
begin
:new.confirmed := 'Y';
:new.complete := 'Y';
-- This will stop at first match rather than trying to count them all
select count(*) into v_NumRows from dual
where exists ( select null
from
bond.book b,
bond.bond o,
:new t
where b.book = o.book
and t.bond = o.bond
and b.book = 99);
if v_NumRows > 0
then
:new.reconciled := 'Y';
end if;
end;
Errors for TRIGGER BOND_TRADE_COMPLETE_TRADE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/3 PLS-00049: bad bind variable 'NEW'
12/3 PLS-00103: Encountered the symbol "" when expecting one of the
following:
( <an identifier> <a double-quoted delimited-identifier>
The symbol "" was ignored.

Can I refer to the new records as ":new" in join above?

Thanks

 

[Dagon]

You seem to be trying to refer to :new as a table. You can't do that.

 

[dbalearner]

As a matter of interest as Dagon suggested it appears that one cannot join an existing table with :new in the trigger code like the code below. If not what is the alternative?

select count(*) into v_NumRows from dual
where exists ( select null
from
bond.book b,
bond.bond o,
:new t
where b.book = o.book
and t.bond = o.bond
and b.book = 99);


Thanks