Professional Web Applications Themes

Empty Trigger - IBM DB2

I need to create an empty Trigger Or a Trigger which has only comments in it. I tried this but it does not get created ....Gives Trigger creation errors. This is what I tried : ---------- create trigger e_trig after INSERT on c REFERENCING NEW AS NEW for each row mode db2sql -- This is a Dummy Trigger [IBM][CLI Driver][DB2] SQL0969N There is no message text corresponding to SQL error "-20100" in the message file on this workstation. The error was returned from module "DSNHPARS" with original tokens "2 -104 42601 -,( END SET CALL DROP FREE HOLD LOCK OPEN ...

  1. #1

    Default Empty Trigger

    I need to create an empty Trigger Or a Trigger which has only comments
    in it.
    I tried this but it does not get created ....Gives Trigger creation
    errors.

    This is what I tried :
    ----------

    create trigger e_trig after INSERT on c
    REFERENCING NEW AS NEW for each row mode db2sql
    -- This is a Dummy Trigger


    [IBM][CLI Driver][DB2] SQL0969N There is no message text
    corresponding to SQL error "-20100" in the message file on this
    workstation. The error was returned from module "DSNHPARS" with
    original tokens "2 -104 42601 -,( END SET CALL DROP FREE HOLD LOCK
    OPEN PARM ALTER BEGI". SQLSTATE=56059

    Execution Failed!
    ----------

    Also tried this
    --------
    create trigger tI_d1 after INSERT on d
    REFERENCING NEW AS NEW for each row mode db2sql
    BEGIN ATOMIC
    -- comments go here
    END

    [IBM][CLI Driver][DB2] SQL0104N An unexpected token "END" was found
    following "". Expected tokens may include: ";". SQLSTATE=42601

    Execution Failed!
    ----------
    smitesh Guest

  2. #2

    Default Re: Empty Trigger

    smitesh <smitesh_j> wrote:
    > I need to create an empty Trigger Or a Trigger which has only comments
    > in it.
    > I tried this but it does not get created ....Gives Trigger creation
    > errors.
    >
    > This is what I tried :
    > ----------
    >
    > create trigger e_trig after INSERT on c
    > REFERENCING NEW AS NEW for each row mode db2sql
    > -- This is a Dummy Trigger
    >
    The CREATE TRIGGER statement requires in its body a mandatory
    "SQL-procedure-statement", which is any of the statements listed under
    "compound SQL (dynamic) in the SQL Reference.
    > Also tried this
    > --------
    > create trigger tI_d1 after INSERT on d
    > REFERENCING NEW AS NEW for each row mode db2sql
    > BEGIN ATOMIC
    > -- comments go here
    > END
    Same as above; at least one statement is required in the BEGIN ... END
    block.

    You could add a dummy statement, however:

    CREATE TRIGGER t1 AFTER INSERT ON d
    REFERENCING NEW AS n
    FOR EACH ROW
    MODE DB2SQL
    BEGIN ATOMIC
    -- some comment
    VALUES (1);
    END

    What exactly is the use of such a trigger if it shouldn't do anything at
    all?

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Stolze Guest

  3. #3

    Default Re: Empty Trigger

    I need to create a dummy trigger body so that user can latter add his
    cutomized sql statements over there. This trigger will be created
    along with some other tables and this is required so that we do not
    get exection failures while creating such schema in DB. Oracle
    delebaratly provides such capability.

    In case of ORACLE we need to add "NULL;" in the trigger body for it to
    compile/create a valid trigger. Is there anything similar for DB2. I
    actually dont want to put Declare statements...instead just some
    comments stating that this trigger is for future use and can be
    modified latter.

    -Smitesh
    smitesh Guest

  4. #4

    Default Re: Empty Trigger

    I think you can use SIGNAL.

    http://publib.boulder.ibm.com/infocenter/db2help/index.jsp
    search : SIGNAL statement


    PM



    PM Guest

  5. #5

    Default Re: Empty Trigger

    smitesh <com> wrote:
     

    I still don't understand that. There is no ALTER TRIGGER statement.
    Therefore you would have to drop the trigger and create a new one. So
    what's the point of the empty trigger?

    Also, what kind of "execution failures" do you expect if the trigger would
    not be present? One comes to my mind: the schema in which the trigger is
    to be placed does not exist and the user who will create the trigger later
    does not have the IMPLICIT_SCHEMA privilege. But I am not sure this is
    what you see as an issue.
     

    As I said in the other post, you could place a "VALUES (1);" statement in
    the trigger body.

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Guest

  6. #6

    Default Re: Empty Trigger

    com (smitesh) wrote in message news:<google.com>... 

    Hi,

    I've played around with triggers and SP quite a lot and if you really
    want to have a comment why don't you write your trigger body as a
    select statement from sysdummy1... I know it's a bit of cheating but
    it'll get you there. Thing is though in DB2 you can't change a
    Trigger you have to drop and recreate it...and then you have to be
    really careful because DB2 executes the oldest on first and the
    youngest one last...

    so if the first (oldest) trigger multiplies a certain value times 5
    and the second trigger (youngest) adds two to this value... suppose
    you want to "modify" the first trigger then you have to drop and
    recreate in and have it for example multiply by 6....but now this is
    the youngest trigger

    so suppose original value is 3
    before change you get 3*5 = 15 +2 = 17
    after change you get 3+2 = 5 * 6 = 30

    be aware of this !! I added the statement you could use to comment but
    I don't see the purpose of it.


    select 'this is where future trigger sql has to be places'
    from sysibm.sysdummy1

    with regards,

    Kurt Struyf
    senior consultant competence partners belgium
    Kurt Guest

Similar Threads

  1. #26380 [Opn]: empty($SimpleXMLObject) doesn't return true when empty
    By bart at mediawave dot nl in forum PHP Development
    Replies: 0
    Last Post: November 24th, 12:58 PM
  2. Replies: 0
    Last Post: October 16th, 05:19 AM
  3. Replies: 0
    Last Post: October 15th, 06:20 PM
  4. Trigger Help
    By Mike in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 9th, 07:28 PM
  5. Trigger key
    By Elisabeth Smith in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 2nd, 09:22 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139