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

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!
pvsbandiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
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

pvsbandiAuthor 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 AnalystCommented:
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...
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

pvsbandiAuthor 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.
slightwv (䄆 Netminder) 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 DeveloperCommented:
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).
pvsbandiAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.