How to use the converted DDL script to create trigger?

Try something like
CREATE TRIGGER XYZ
before insert on branch
referencing new as N
for each row mode db2sql
begin atomic
set N.rcdversion = 1;
set N.SOMEDATE = CURRENT DATE;
set N.THEUSER = USER;
end

AFAIK you will need a separate trigger for Update. The online doco includes trigger
info in the SQL Reference, and the Application Development Guide.

Try getting the syntax of a single trigger right in the Command Line Processor or the
Command Center. Once you've got the command figured pretty well then have a go at the
script.

-- Greg

Sherlyn wrote:

> Hi,
>
> I would like to convert 2 trigger from ORacle database to DB2 database.
> Do you know where & what command i shall type to run the DDL script file
> i had generated from the SQL-Coversion Workbench Tool in IBM DB2 environment?
>
> Below is the DDL file for create triggers:
>
> CREATE TRIGGER DAtrg_bfrow_branch
> before insert or update on branch
> for each row
> begin
> if inserting then
> :new.rcdversion := 1;
> :new.dtcreated := SYSDATE;
> :new.tmcreated := SYSDATE;
> :new.usrcreated := USER;
> :new.dtupdated := SYSDATE;
> :new.tmupdated := SYSDATE;
> :new.usrupdated := USER;
> elsif updating then
> :new.rcdversion := :old.rcdversion + 1;
> :new.dtupdated := SYSDATE;
> :new.tmupdated := SYSDATE;
> :new.usrupdated := USER;
> end if;
> end;
> ;
>
> CREATE TRIGGER "EQUITY".DAtrg_afrow_branch
> after insert or update on branch
> for each row
>
> declare
> iErrCode integer;
> szErrMsg char(501);
>
> begin
>
> if (:old.rcdversion is not null) and
> (:new.rcdversion < :old.rcdversion) then
> DA_pkg_exphndlr.DA_sp_disperr(DA_pkg_exphndlr.err_inv_rcdversion, '');
> end if;
>
> end;
> ;
>
> ========================================================
> To run the create trigger scripts in the 'Command Window', i type the following:
>
> C:\output\> DB2 -f trigger.ddl -t -r output.txt -s -l errorlog.txt
>
> ERROR FOUND:-
> DB21034E The command was processed as an SQL statement because it was not
> a valid Command Line Processor command. During Sql processing it returned:
> SQL0104N An unexpected token "CREATE TRIGGER DAtrg_bfrow_branch before i"
> was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<revoke>".
> SQLSTATE=42601
>
> I am new to DB2, not much sure about the DB2 language.
> Can anyone tell me what's wrong with my triggers?
> Thank you.
>
> Regards,
> Sherlyn

Hi

How about something like..
---
CREATE TRIGGER "EQUITY".DAtrg_afrow_branch
no cascade before update on branch
referencing new as N, old as O
for each row mode db2sql
when (O.rcdversion is not null and N.rcdversion < O.rcdversion)
signal sqlstate '75000' ('Version cannot decrease')
----
which will raise an SQL error with number 75000 and text as shown.
Note it's only on update, but by the looks of your example it's not really necessary
for an insert.

Hope this helps
--Greg

Sherlyn wrote:

> Hi,
>
> How about the 'CREATE TRIGGER "EQUITY".DAtrg_afrow_branch - after insert
> on branch'? Can anyone show me on how to write the below if-then-else statement
> for the CREATE TRIGGER "EQUITY".DAtrg_afrow_branch?
>
> I would much appreciate of you prompt to me, thank you.
>
> Regards,
> Sherlyn
>
> Greg Nash <gnash@namoicotton.com.au> wrote:
> >
> >Try something like
> >CREATE TRIGGER XYZ
> > before insert on branch
> > referencing new as N
> > for each row mode db2sql
> > begin atomic
> > set N.rcdversion = 1;
> > set N.SOMEDATE = CURRENT DATE;
> > set N.THEUSER = USER;
> > end
> >
> >AFAIK you will need a separate trigger for Update. The online doco includes
> trigger
> >info in the SQL Reference, and the Application Development Guide.
> >
> >Try getting the syntax of a single trigger right in the Command Line Processor
> or the
> >Command Center. Once you've got the command figured pretty well then have
> a go at the
> >script.
> >
> >-- Greg
> >
> >Sherlyn wrote:
> >
> >> Hi,
> >>
> >> I would like to convert 2 trigger from ORacle database to DB2 database.
> >> Do you know where & what command i shall type to run the DDL script file
> >> i had generated from the SQL-Coversion Workbench Tool in IBM DB2 environment?
> >>
> >> Below is the DDL file for create triggers:
> >>
> >> CREATE TRIGGER DAtrg_bfrow_branch
> >> before insert or update on branch
> >> for each row
> >> begin
> >> if inserting then
> >> :new.rcdversion := 1;
> >> :new.dtcreated := SYSDATE;
> >> :new.tmcreated := SYSDATE;
> >> :new.usrcreated := USER;
> >> :new.dtupdated := SYSDATE;
> >> :new.tmupdated := SYSDATE;
> >> :new.usrupdated := USER;
> >> elsif updating then
> >> :new.rcdversion := :old.rcdversion + 1;
> >> :new.dtupdated := SYSDATE;
> >> :new.tmupdated := SYSDATE;
> >> :new.usrupdated := USER;
> >> end if;
> >> end;
> >> ;
> >>
> >> CREATE TRIGGER "EQUITY".DAtrg_afrow_branch
> >> after insert or update on branch
> >> for each row
> >>
> >> declare
> >> iErrCode integer;
> >> szErrMsg char(501);
> >>
> >> begin
> >>
> >> if (:old.rcdversion is not null) and
> >> (:new.rcdversion < :old.rcdversion) then
> >> DA_pkg_exphndlr.DA_sp_disperr(DA_pkg_exphndlr.err_inv_rcdversion,
> '');
> >> end if;
> >>
> >> end;
> >> ;
> >>
> >> ========================================================
> >> To run the create trigger scripts in the 'Command Window', i type the
> following:
> >>
> >> C:\output\> DB2 -f trigger.ddl -t -r output.txt -s -l errorlog.txt
> >>
> >> ERROR FOUND:-
> >> DB21034E The command was processed as an SQL statement because it was
> not
> >> a valid Command Line Processor command. During Sql processing it returned:
> >> SQL0104N An unexpected token "CREATE TRIGGER DAtrg_bfrow_branch before
> i"
> >> was found following "BEGIN-OF-STATEMENT". Expected tokens may include:
> "<revoke>".
> >> SQLSTATE=42601
> >>
> >> I am new to DB2, not much sure about the DB2 language.
> >> Can anyone tell me what's wrong with my triggers?
> >> Thank you.
> >>
> >> Regards,
> >> Sherlyn
> >