I am reading a paper called "Real Time Delta Extraction Based on Triggers to Support Data Warehousing" from the IEEE and they proposed the following code:
CREATE TRIGGER cdc_table ON db.table AS DECLARE @OPER, @INST, @TAB, @PK, @ID Select @TAB=table of altered tuple Select @PK= primary key of @TAB Select @INST= instance in which @TAB belongs Begin IF(@OPER = DELETE) Select @ID= ID from tuple before deletion; ELSE Select @ID= ID from tuple after insertion or update; ENDIF Insert into log table @ID, @PK, @TAB, @INST, @OPER; END;
In the line 5 they are asking me to SELECT the instance of the table I am altering. How can I do that?
The log table is defined as follows:
- log id – an integer type field, having a sequential number, that stores the execution order of the operations;
- instance – a text type field, stores the name of the instance whose tuple was altered;
- table – a text type field, stores the name of the table whose tuple was altered;
- operation – stores the type of operation that was executed: INSERT, UPDATE or DELETE;
- pk column – a text type field, stores the name of the column that was used as a unique identifier in the table whose tuple was altered;
- tuple id – value of the unique identifier of the altered tuple.