I am trying to generate unique "rowids" for all the rows in all of the tables in my database (not just unique ids across each table - so identity columns won't help me). I am using a trigger in order to generate this unique value automatically on inserts (using the generate_unique() method). I need to load data from files in order to populate my tables, so I can't use simple inserts and I am using the db2import utility, as I know the load utility does not fire triggers. My problem is that even with this import utility, my trigger does not get fired. What I did is the following:
create table customer
( ROW_ID CHAR(13) FOR BIT DATA
, c_custkey integer not null
, c_name varchar(25) not null
, c_address varchar(40)
, c_nationkey integer
, c_phone char(15)
, c_acctbal float
, c_mktsegment char(10)
, c_comment varchar(117)
CREATE TRIGGER UPDATE_CUSTOMER
BEFORE INSERT ON CUSTOMER
REFERENCING NEW AS NEW_CUSTOMER
FOR EACH ROW MODE DB2SQL
SET NEW_CUSTOMER.ROW_ID = GENERATE_UNIQUE();
import from customer.tbl OF del
modified by coldel|
method P (1, 2, 3, 4, 5, 6, 7, 8)
insert into customer(c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment );
When I issue select count(distinct timestamp(row_id)) from customer; I get a 0. How can I convince import to activate the trigger? I have to mention that the trigger gets fired when I issue simple INSERT statements.
As far as I know, I can't modify new row/table transition variables with an AFTER INSERT trigger. Anyways, this is not the problem. The problem is that the trigger gets fired after a simple INSERT into CUSTOMERS statement, but not when I'm loading data using the IMPORT utility.The documentation explicitly specifies that the IMPORT utility (unlike LOAD) uses underlying INSERT statements to load the data, so logically speaking, the trigger should get fired. Are triggers deactivated by default on IMPORT executions? If so, how can I specify that I want my triggers to be fired on IMPORT? If not, then is this a bug?
Thanks! That was the problem indeed (no bug with import). I was confused because (as a newbie) I was typing my sql queries inside the command window, and when selecting row_id as such, nothing showed up in the results tab (I mean, the cells were all empty). I switched to the command window and I can see all the distinct char for bit data now Thank you so much again!