Is it possible for me to implement a unique key constraint using a trigger? That every row that would be inputted into the table would be checked if that certain column is already present in the table, if it is then it would be rejected.
Why can't you just implement a unique contraint on the table instead???
if the column is defined as not null then:
alter table t2 add constraint c1 unique(col-name)
in case it is not defined as not null.
a. Then use export utility to export the the data.
b. Drop the table and recreate it using the not null constraint for the column.
c. alter table t2 add constraint c1 unique(col-name)
d. use import/load utlity to get data back into table.
what happens after i insert a duplicate item to the table? would it reject it or cause an error? what i want to happen is for db2 to trap the error so it won't cause any error message when a duplicate item is inserted into the table, is that possible?
The insertion is rejected. I just tried it and it generated a SQL803N error. But if you are trying multiple insertions(using db2 -tvf ) then only the rows violating the constraints are rejected, all others are inserted.
I also tried creating a trigger to implement the unique constraint .
db2 create trigger trig1 no cascade before insert on t13 referencing NEW as n1 for each row mode db2sql when (exists(Select 1 from t13 where a=n1.a)) begin atomic signal sqlstate '70001' ('insertion error'); end
It does the same thing as the unique constraint does. Rejects violating rows, inserts all others.
Incase you want to reject the NULL values also,create 2 'BEFORE INSERT' triggers.
db2 create trigger trig5 no cascade before insert on t13 referencing NEW as n1 for each row mode db2sql when ( n1.a is null ) begin atomic signal sqlstate ' 70001' ('value is null'); end
db2 create trigger trig2 no cascade before insert on t13 referencing NEW as n1 for each row mode db2sql when (exists(Select 1 from t13 where a=n1.a)) begin atomic signal sqlstate '70002' ('Duplicate key'); end
hey tnx. well it worked out well....is it possible to produce a warning instead of an sqlstate error? and is it possible that if the item is already present that i would then produce an update statement?
Or is it possible that if a duplicate error would occur then it would reject the row and do nothing, i need it to produce no error message or produce just a warning message so that it won't annoy the user using the application is that possible?
Has anyone of you guys tried using Warehouse Manager which comes with DB2 UDB or Visual Warehouse? Well i tried using that trigger over that application and it keeps popping that error message and the load or insert to the table is always stopped.