Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Triggers -> Comparing NEW to OLD

Status
Not open for further replies.

wwtf

Programmer
Jul 3, 2006
3
US
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;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top