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;
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;