How to use the converted DDL script to create trigger?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: How to use the converted DDL script to create trigger?

Hybrid View

  1. #1
    Sherlyn Guest

    How to use the converted DDL script to create trigger?


    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 := ld.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 (ld.rcdversion is not null) and
    (:new.rcdversion < ld.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

  2. #2
    Greg Nash Guest

    Re: 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 := ld.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 (ld.rcdversion is not null) and
    > (:new.rcdversion < ld.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



  3. #3
    Sherlyn Guest

    Re: How to use the converted DDL script to create trigger?


    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 := ld.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 (ld.rcdversion is not null) and
    >> (:new.rcdversion < ld.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

    >



  4. #4
    Greg Nash Guest

    Re: How to use the converted DDL script to create trigger?

    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 := ld.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 (ld.rcdversion is not null) and
    > >> (:new.rcdversion < ld.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

    > >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center