Trigger UPDATE with ObjectPtr

[Ivan Smahin]

Hello Ruslan,

Friday, January 18, 2008, 8:40:08 AM, you wrote:

> On 18/1/08 4:41 AM, "danhc at mail.dk" <danhc at mail.dk> wrote:

> Hi Dan,

>> Could You help me on this:
>> 
>> How do I in SQL create a Trigger that on an update of 1 table updates
>> records in an other table, but only the ones that have an ObjectPtr
>> pointing back at the table containing the Trigger.
>> 
>> I can't find out how to refer to the RecID og the Table containing the
>> Trigger.
>  
>> I am using Valentina 3.5.1 for RealBasic and Mac OSX 10.4.11

> You can Reffer RecID of updated record using

>     NEW.RecID       for BEFORE UPDATE trigger
>     OLD.RecID       for AFTER UPDATE trigger

> It seems to me, you need use Stored Procedure to update records of linked
> table. And call this procedure from trigger.

> Ivan ?

I would recommend to use sql from the very first steps...
It is hard enough to read all that conditions in plain english...

Well, so you have:

CREATE TABLE t1 ( f1 LONG );
CREATE TABLE t2 ( f1 LONG, ptr OBJECTPTR REFERENCES t1 );

INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);

INSERT INTO t2 VALUES (0, 0);
INSERT INTO t2 VALUES (0, 2);
INSERT INTO t2 VALUES (0, 0);

--
Assume  we  are  updating t1.RecID == (1,2).
Trigger should do some updates for t2.RecID == 2.
Right?

It  is  simple - any ptr not-null and not-0 value means current record
is linked to t1.

So:
CREATE TRIGGER trigger1
BEFORE UPDATE ON t1
FOR EACH ROW
BEGIN
     UPDATE t2 SET f1 = 1 WHERE ptr > 0;
END;

--
Assume  we  are  updating t1.RecID == (1,2).
But trigger should be fired for t1.RecID == 2 and do some updates for t2.RecID == 2.

So:
CREATE TRIGGER trigger1
BEFORE UPDATE ON t1
FOR EACH ROW
BEGIN
     UPDATE t2 SET f1 = NEW.RecID WHERE ptr = NEW.RecID;
END;

Trying  it  I've  found  the  bug.  It  would  be fixed today and will be
available in next beta.

-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com