Hi guys,

Thanks for your help. I should say this first. ^^

Anyway, I have an another question for you guys.

I want to create a trigger which only fires when a certain column has
changed. Can anyone help me out this?

Many thanks in advance,

While you can't fire the trigger ONLY when a column has changed, you
can fire an UPDATE trigger that has an IF statement to compare the old
and new values. The trick is in the REFERENCING clause:

CREATE TRIGGER trPersonUpdate AFTER UPDATE ON Person
REFERENCING NEW AS N
REFERENCING OLD AS O
FOR EACH ROW
BEGIN
IF N.ID <> O.ID THEN ....
END;
See the online manuals for the proper syntax & everything...
Goldstar Software Inc.
Providing Pervasive.SQL Training & Services
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 03/27/06 ***

portCo wrote:

Thanks Bill,

I've got another question about your suggestion. How can I use
Referencing NEW and OLD at the same time? I can't find any help or
manual which contain this issue.

could you please tell me how to do it ? or tell me where I can find?

Thanks alot,

Sorry -- the syntax might be slightly off. Here's the syntax from the
docs:

CREATE TRIGGER trigger-name before-or-after ins-upd-del ON table-name
[ ORDER number ]
[ REFERENCING referencing-alias ] FOR EACH ROW
[ WHEN proc-search-condition ] proc-stmt

and

referencing-alias ::= OLD [ AS ] correlation-name [ NEW [ AS ]
correlation- name ]

With this correction, it should look like this:
CREATE TRIGGER trPersonUpdate AFTER UPDATE ON Person
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
BEGIN
IF N.ID <> O.ID THEN ....
END;
BB


portCo wrote:

Thank very much Bill.

I've got one more question for you if you don't mind.

I've tried to create a trigger with your suggestion. However, I've got
an error message.

Here is my trigger.

CREATE TRIGGER UptTrig
AFTER UPDATE ON psqlTest
REFERENCING OLD AS Olddata NEW AS Indata

FOR EACH ROW
BEGIN
IF Indata.tName <> Olddata.tName THEN
INSERT INTO psqlTemp (id, tName, tDesc, tOrderDate, cond)
VALUES(Indata.id, Indata.pName, Indata.pDesc, Indata.pOrderDate,
'U')
END;


The error message is "[Pervasive][ODBC Client
Interface][LNA][Pervasive][ODBC Engine Interface][Data Record
Manager][SPEng]9: 'END': Syntax error"

FYI, I am using psql 2000i sp2.

Thanks again.

You are aware that the SQL Syntax is all in the online documentation,
right?

The proper syntax for the IF statement is:
IF <condition> THEN
<statement>
[ELSE
<statement>]
END IF;

The square brackets indicate that the ELSE is optional.

Also, each <statement> must be terminated with a semicolon.

So, to fix up your proc, add a semicolon at the end of your INSERT
statement, add the "END IF;" to close the IF statement, and you should
be OK.
Goldstar Software Inc.
Providing Pervasive.SQL Training & Services
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 03/27/06 ***

portCo wrote: