Postgresql - How to create triggers for old and new values in CDC Type-2

pvsbandi
pvsbandi used Ask the Experts™
on
Hi,

  We are on Postgresql 9.7
There is a table "Table-1" with the following fields.
ID                Name                DOB               SSN                   Start_dt            End_dt
1                   Kevin               2/3/2001      xxx-xx-2354        1/1/2018         12/31/9999

Open in new window

Whenever there is an update, let's say, to the DOB, the current row gets end dated and a new row gets inserted, like below:
ID                Name                DOB               SSN                   Start_dt            End_dt
1                   Kevin               2/3/2001      xxx-xx-2354        1/1/2018         3/17/2019
2                   Kevin               2/3/2000      xxx-xx-2354        3/17/2019         12/31/9999

Open in new window


My question is related to a trigger to capture the old and new values of the DOB , which is a mix of UPDATE and INSERT.
First off, how can we sense if it is a change to the existing entry or a new insert, in this CDC Type-2 scenario?

Please help
Thank you!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
I would put a trigger on the table where INSERT/UPDATE is performed and use the NEW.dob and OLD.dob to capture the old(current) value vs. the new value and also use the NOW() or CURRENT_DATE() for the date columns you want to add to the log/audit trail table.
Please don't just take the code below and throw it on a production table but obviously test first and adjust to your needs in a DEV/QA environment.

CREATE OR REPLACE FUNCTION log_dob_changes()
  RETURNS trigger AS
$BODY$
BEGIN
	IF TG_OP = 'INSERT' THEN
		INSERT INTO dob_log (id, dob, ssn,start_dt end_dt)
				VALUES (newid(), NEW.dob, ssn, start_dt, end_dt);
	IF TG_OP = 'UPDATE' THEN
		INSERT INTO dob_log (id, dob, ssn,start_dt end_dt)
				VALUES (newid(), OLD.dob, ssn, NOW(), end_dt);
	END IF;
RETURN null;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION log_dob_changes() OWNER TO dbo;

Open in new window

Author

Commented:
Thanks for your input!

   The problem is that the update only updates the end_Dt field. Insert is where we have the new value.
Question, is, how to use both the actions(Insert & Update) in conjunction to capture the old and new values.

Hope i'm clear.
lcohanDatabase Analyst

Commented:
Sorry I don't quite understand what exactly is needed here - my bad...however you could use

IF TG_OP = 'INSERT' OR IF TG_OP = 'UPDATE'

in the trigger to set desired values if this is what you mean by
how to use both the actions(Insert & Update) in conjunction to capture the old and new values
For any column of the table where trigger action takes place you can use the OLD.ColName and NEW.ColName to get the new value that's going in and the current-OLD value that's being replaced. Hope this helps...
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Sorry, if i confused you.

   Let me try to rephrase:

Whenever there is an update to a field "abc" in the table, it doesn't directly do an update action. It only updates the effective_end_dt field, in that row.
 Then, it inserts a new record with the new value in the "abc" field, with the effective_begin_dt = current_dt and effective_end_dt = 9999-12-31

Since a trigger action only captures an update, or an insert at any given time, we may not be able to handle both the actions at the same time to capture the old and new values.

Please let me know if there are any questions.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Per the docs, column level update triggers are available:
https://www.postgresql.org/docs/9.1/sql-createtrigger.html

Just create the trigger on UPDATE OF effective_end_dt.
ste5anSenior Developer

Commented:
Can you please rephrase your question?

Cause your "table-1" looks already like the dimension table. Thus I don't understand why you're talking about triggers. The dimensional model should be populated by an explicit ETL process.

But when you want to simply  keep a history table, then I would not use a "date to" (End_dt) column , cause this value is implicit in the transactional model.

The update in the dimensional model is pretty simple: insert the new row(s) with a dummy end date. Then do a recalculation (UPDATE) of the end date column using LAG() to get the previous value(s).

Author

Commented:
Hi Stefan,

        The changes in the table are used by another batch process which looks at only those updates done to specific fields like DOB, SSN etc and inserts those records into a batch table.
These batch table entries are used to kick off a batch process.
Let me know if you need more information

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial