On 5/6/2010 4:12 PM, Plugge, Joe R. wrote:
>
> I am trying to create a update trigger on a table that basically will
> only fire when a specific column is updated. I am using version 8.4.3.
>
> My plan of attack was to always fire on any row update, and pass in
> the OLD and NEW column that I want to check.
>
> CREATE TRIGGER check_lockout
>
> AFTER UPDATE ON acct_table
>
> FOR EACH ROW
>
> EXECUTE PROCEDURE
> acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);
>
> This fails with :
>
> [postg...@linux1505 ~]$ cat check_lockout_trig.sql | psql testdb
>
> ERROR: syntax error at or near "OLD"
>
> LINE 4: EXECUTE PROCEDURE
> acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);
>
> What am I doing wrong? Or is there a better way to go about this?
>
You don't call the trigger procedure with the old and new as parameters
new and old are automatically created for the function acct_unlock()
CREATE TRIGGER check_lockout
AFTER UPDATE ON acct_table
FOR EACH ROW
EXECUTE PROCEDURE acct_unlock();
Next the trigger function would look something like this
create or replace function acct_unlock()
returns trigger as
$$
if (OLD.userid <> NEW.password)
do something
end if;
$$
keep in mind the acct_unlock must be returns trigger
Then return either NEW or the OLD record
OLD if not changing the record or NEW if the updated values are to be
stored in the table.
All legitimate Magwerks Corporation quotations are sent in a .PDF file
attachment with a unique ID number generated by our proprietary quotation
system. Quotations received via any other form of communication will not be
honored.
CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally
privileged, confidential or other information proprietary to Magwerks
Corporation and is intended solely for the use of the individual to whom it
addresses. If the reader of this e-mail is not the intended recipient or
authorized agent, the reader is hereby notified that any unauthorized viewing,
dissemination, distribution or copying of this e-mail is strictly prohibited.
If you have received this e-mail in error, please notify the sender by replying
to this message and destroy all occurrences of this e-mail immediately.
Thank you.
