Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2003
    Posts
    22

    Exclamation Unanswered: How can i write a udf containg sql statements?

    I need to create a udf contaning an sql statement.
    I' ve tried to use the syntax 'create....returns...return..' but it doensn't work.
    I suppose this occurred because of version of db2 I have on my platform (ver 6).

    Anyone can help me to convert the following create function to make it work on my platform?

    Create function owner.trigger_enabled(
    v_schema varchar(128),
    v_name varchar(30))
    returns varchar(1)
    return (select acrive from owner.trigger_state
    where trigschema=v_schema and trigname=v_name)

    Please help me.......

  2. #2
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,

    I have checked it on DB2 7.1 on Windows 2000 machine and it works fine.
    I am sure that it will work for version6 as well.
    Anyhow, you have not mentioned any error details here, so as to get the idea of type of error like the priveleges of the user on the objects or any other syntactical error.

    Cheers,
    Prashant

  3. #3
    Join Date
    Feb 2003
    Posts
    22
    I'm sure....it's a problem of syntax.....with ver 7 it works.
    In ver 6 I cannot use the option 'return'....
    Cristiana
    Data Base Administrator
    Bologna
    Italy

  4. #4
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi Chris,

    Yes i think DB2 6 does not alow this.

    Do one thing go through the syntax in the documentation, there must be some alternative.

    On the DB2 command line just type DB2 ? to get the list of supported commands. Since i am using 7.2, i can't help you on that.

    Cheers,
    Prashant

  5. #5
    Join Date
    Feb 2003
    Posts
    22
    If anyone is interested......I WON!!!!!!!!!!!

    Thanks a lot to everyone.
    I've created a trigger that make insert only if there is a row in another table with a value 'y' in column 'active'....
    I cannot believe it.....

    Two days of hard work...but now I WON!!!!!
    Cristiana
    Data Base Administrator
    Bologna
    Italy

  6. #6
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Well done Christiana,

    I liked the way you njoy your victory...
    Do share your victory and let us know the way you did...i guess it is in the When clause of the trigger where you played all the game...

    Cheers,
    Prashant

  7. #7
    Join Date
    Feb 2003
    Posts
    22
    Here there is the solution...i've not used when clause (ehehehe!)

    CREATE TRIGGER TRIG1
    AFTER UPDATE ON TAB_PROVA
    REFERENCING OLD AS OLD_RIGA
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    INSERT INTO TAB_PROVA_2
    SELECT OLD_RIGA.CAMPO_1, OLD_RIGA.CAMPO_2
    FROM TRIGGER_STATE WHERE
    TRIGSCHEMA='DBA' AND TRIGNAME = 'TRIG1' AND ACTIVE = 'Y'

    ahahaha...db2....I've cathced you!!!!!!!
    Cristiana
    Data Base Administrator
    Bologna
    Italy

Posting Permissions

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