Results 1 to 13 of 13

Thread: DB2 to Postgre

  1. #1
    Join Date
    Feb 2008
    Posts
    7

    Question Unanswered: DB2 to Postgre

    Heya
    I have some problems to translate db2 triggers to postgres.
    Here is a few cases:
    (in DB2)
    CREATE TRIGGER trigg
    AFTER UPDATE OF something ON table
    ;;
    CREATE TRIGGER trigg2
    NO CASCADE BEFORE INSERT ON table
    ;;

    would be cool to get some examples.
    thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12

  3. #3
    Join Date
    Feb 2008
    Posts
    7
    hmm. only way to make that kind of triggers is to make them with C?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    No you can use PL/PGSQL.

    I corrected the initial link about 5 seconds after replying (you must have been quick...)

    The current URL points to the part of the manual that describes writing triggers in PL/PGSQL

  5. #5
    Join Date
    Feb 2008
    Posts
    7
    okies. thanks mate, if u have example trigger that updates specific column it would help a lot

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    It's in the manual:
    Code:
    CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
        BEGIN
            -- Check that empname and salary are given
            IF NEW.empname IS NULL THEN
                RAISE EXCEPTION 'empname cannot be null';
            END IF;
            IF NEW.salary IS NULL THEN
                RAISE EXCEPTION '% cannot have null salary', NEW.empname;
            END IF;
    
            -- Who works for us when she must pay for it?
            IF NEW.salary < 0 THEN
                RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
            END IF;
    
            -- Remember who changed the payroll when
            NEW.last_date := current_timestamp;
            NEW.last_user := current_user;
            RETURN NEW;
        END;
    $emp_stamp$ LANGUAGE plpgsql;
    
    CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
        FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
    (copied directly from the link I posted)

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Keep this in mind. With PostgreSQL, the Rule system may also be employed in place of many insert/update triggers. Rules are typically more efficient that are triggers.

    The above sort of trigger can be implemented with a rule.
    Code:
    CREATE TABLE shoelace_log (
        sl_name    text,          -- shoelace changed
        sl_avail   integer,       -- new available value
        log_who    text,          -- who did it
        log_when   timestamp      -- when
    );
    
    CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
        WHERE NEW.sl_avail <> OLD.sl_avail
        DO INSERT INTO shoelace_log VALUES (
                                        NEW.sl_name,
                                        NEW.sl_avail,
                                        current_user,
                                        current_timestamp
                                    );
    In the above case, when table shoelace_data is updated, records would be saved in table shoelace_log, with audit trail values.

    Ref http://www.postgresql.org/docs/8.3/i...es-update.html

    Now, from http://www.varlena.com/GeneralBits/18.php

    Quote Originally Posted by varlena
    The decision factor for rules vs. triggers is that rules are for rewriting the query and triggers are for changes executed on rows. The timing is different. Rules are invoked before a query gets executed and may affect the statement to be executed. The rule is only invoked at the start, but the ultimate statement may affect many rows.

    A trigger, on the other hand, is a reaction to a change of data. During the statement execution, if there is a trigger PER ROW, each row affected will fire off the trigger. The triggers PER STATEMENT are not yet implemented [in 2003] so they are not considered here.

    It is a common mistake to try to use a rule to track input and delete counts, for example. A rule which adds or subtracts a counter in some table will fire once, regardless of whether the statement affects one or many rows. In the example in the item above, if rules were used instead of triggers then when several rows were deleted by a single SQL statement the count would only have been decremented by one. Likewise, when we inserted multiple rows in one statement, the row count would have been incremented by one.

    A good use of rules is to enable updates, inserts and deletes for views. These rules can redirect the data into logical statements operating on the underlying tables.
    Last edited by loquin; 02-22-08 at 18:03.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Feb 2008
    Posts
    7
    Thanks again. thoug hi can survive with this information, but prolly i'll be back when i get to procedures =D

  9. #9
    Join Date
    Feb 2008
    Posts
    7
    rofl. here comes a new question (still about triggers).
    i put the whole db2 code here, hopely u can give me some hints to make it with postgre
    CREATE TRIGGER DEL_REMOV_SECS
    AFTER DELETE ON UPDATED_SECS
    REFERENCING OLD_TABLE AS o
    FOR EACH STATEMENT MODE DB2SQL
    BEGIN ATOMIC
    DELETE FROM Sector
    WHERE LocaleID=(SELECT DISTINCT LocaleID FROM o)
    AND SectionID NOT IN (SELECT SectionID FROM o);
    END

  10. #10
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    The way I understand it, since the trigger is operating on another table, it should be written as a RULE.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  11. #11
    Join Date
    Feb 2008
    Posts
    7
    nods. just wondering that old table as o. is there way to do same thing in postgre?

  12. #12
    Join Date
    Mar 2007
    Posts
    623
    I do not know DB2, but the way you use "O" does not seem valid according to its manual.

    Seems to me you are trying to force referential integrity. If (LocaleID, SectionID) is at least unique in SECTOR, you can use foreign key with ON DELETE CASCADE (I found it in this article about DB2 too).
    But that is just a guess as I do not know, what those SELECTs from O are intended to return and the reason for writing the conditions that way.

  13. #13
    Join Date
    Feb 2008
    Posts
    7
    okies. im working with procedures atm and i have a simple question. is it possible to return output parameter and refcursor in the same function? and if it is what is the valid syntax for it? (koo BIGINT, OUT foo INTERGER ) RETURNS REFCURSOR ( this isnt valid!!)

Posting Permissions

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