Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Question Unanswered: Trigger and Objects

    Hello everyone,

    Is there any documentation about how to get the reference of :old or :new in a trigger, when it concerns relational-object table ?

    My problem is that I'm using 2 tables A and B, each one stores a specific object, A_t and B_t.
    A_t.refB, an attribute of A_t, is a reference (REF) on B_t, and its scope is restricted to B.

    Before dropping a row of B, a trigger checks if the B_t object is still referenced by the table A, to avoid dangling references.
    But that doesn't work...

    CREATE TRIGGER xx BEFORE DELETE ON B
    BEGIN
    c NUMBER;
    SELECT COUNT(VALUE(a)) INTO c FROM A a WHERE (a.refB = REF(:old));
    IF ... THEN RAISE_APPLICATION_ERROR(...); ...
    END;

    The error is : bad bind variable 'OLD'
    Maybe it's necessary to store into B_t its own OID pointer ?

    Thank you in advance.

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Try ...

    CREATE TRIGGER xx BEFORE DELETE ON B
    REFERENCING NEW AS NEW OLD AS old
    FOR EACH ROW
    ...


    Then you can ref columns as
    where :new.column_name = ld.column_name

    HTH
    Gregg

  3. #3
    Join Date
    Jan 2004
    Posts
    3
    Maybe I'm in the wrong, but I would like to compare references or OID, not columns values... How using REFERENCING can help ?

  4. #4
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Accroding to the doc and memory the "new" and "old" are built-in for table triggers. You can just use :new.column and ld.column without the "reference as" contruct.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Petrus
    Maybe I'm in the wrong, but I would like to compare references or OID, not columns values... How using REFERENCING can help ?
    You are right, your issue is not helped by adding a REFERENCING clause.

    As for what the answer actually is, I have no idea. I have never created a table using REF and I never intend to. Apart from making it harder to query the database (and to write triggers), what does it do for you?

    If the table was built using good old relational methods, you wouldn't even need the trigger, you would create a foreign key.

  6. #6
    Join Date
    Jan 2004
    Posts
    3
    Indeed, it's not an obligation to use object table, but references are abstraction for forein keys, although they are sometimes harder to manage...

    For example you can write :
    SELECT p.name
    FROM Personns p
    WHERE p.adress.town.name LIKE 'xx';

    where town is an attribute of adress, which is an attribute of personn.
    That is also useful when you're using JPublisher which automaticly generates the java classes corresponding to the database objects.

    FinallyI "solve" my problem by adding an attribute into the B_t object :
    mySelf REF B_t;

    which is initialized at the creation :
    UPDATE B b SET b.mySelf = REF(b)
    WHERE xxx;

    The trigger is now :

    CREATE TRIGGER xx BEFORE DELETE ON B
    varA A_t;
    BEGIN
    SELECT VALUE(a)
    INTO varA
    FROM A a
    WHERE (a.refB = ld.mySelf);

    RAISE_APPLICATION_ERROR(...);

    EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
    END;

Posting Permissions

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