×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Triggers -> Comparing NEW to OLD

Triggers -> Comparing NEW to OLD

Triggers -> Comparing NEW to OLD

(OP)
Greetings,

I'm creating a history table that will record any changes to a record. Problem is that I have to update multiple records in one shot where some of the records may or may not contain changes. I fire a trigger after Insert/Update and the trigger function looks like this.

I was hoping I'd be able to do

IF NEW <> OLD THEN
-- do insert here...
END IF;

However, above doesn't work so I had to do whole bunch of IF's which I'd love to avoid.

Any help is greatly appreciated.

~k6

CREATE OR REPLACE FUNCTION update_client_item_history() RETURNS TRIGGER AS
'
DECLARE
flag INT;

BEGIN

IF NEW.item_type_id <> OLD.item_type_id THEN flag := 1; END IF;
IF NEW.creditor_activity_id <> OLD.creditor_activity_id THEN flag := 1; END IF;
IF NEW.bureau_activity_id <> OLD.bureau_activity_id THEN flag := 1; END IF;
IF NEW.item_status_id <> OLD.item_status_id THEN flag := 1; END IF;
IF NEW.item_description_id <> OLD.item_description_id THEN flag := 1; END IF;
IF NEW.item_phase_id <> OLD.item_phase_id THEN flag := 1; END IF;
IF NEW.acct_name <> OLD.acct_name THEN flag := 1; END IF;
IF NEW.acct_num <> OLD.acct_num THEN flag := 1; END IF;
IF NEW.balance <> OLD.balance THEN flag := 1; END IF;
IF NEW.dla <> OLD.dla THEN flag := 1; END IF;
IF NEW.updated_risk <> OLD.updated_risk THEN flag := 1; END IF;

IF flag = 1 THEN

INSERT INTO client_item_history( item_id,
author_entity_id,
item_type_id,
creditor_activity_id,
bureau_activity_id,
item_status_id,
item_description_id,
item_phase_id,
acct_name,
acct_num,
balance,
dla,
start_risk,
updated_risk )
VALUES( NEW.id,
NEW.author_entity_id,
NEW.item_type_id,
NEW.creditor_activity_id,
NEW.bureau_activity_id,
NEW.item_status_id,
NEW.item_description_id,
NEW.item_phase_id,
NEW.acct_name,
NEW.acct_num,
NEW.balance,
NEW.dla,
NEW.start_risk,
NEW.updated_risk );
END IF;

RETURN NEW;
END;
'
LANGUAGE plpgsql;

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close