Hey everyone , i am relatively new in triigers and i was hoping for some help on this one.

I have succefully created a triger to check for the email correctness.

And i want one more to check that the same product is not entered twice on a day.

I have 3 tables. the products, the catalog and the entry.

So i want to add a produnt into a given catalog but i do not want a product to be entered twich on the same date

How can i do that with triggers ?
the date is on the catalog table and whenever i enter an entry ill have to check that the day is not the same.

Some help would be greatly appreciated !

CREATE TRIGGER check_date
BEFORE INSERT ON entry
REFERENCING NEW AS n
FOR EACH ROW MODE
BEGIN ATOMIC
FROM product s, catalog x, entry e
WHERE s.sno = e.esno and
e.encode = x.excode and
n.x.date = x.date
SIGNAL SQLSTATE '75001'
(' There cannot be two entries on the same day');
END

This is something that i am trying to do, but it is wrong, i didnt run it, cause i now it is wrong , so some help

Why don't you just create a unique index on the columns?

Andy

does not look like a complete SQL statement.

well a unique is one way to solve that one, as also doing that programmatically ( java )

but i am looking for a trigger solution.


So what else should i include in order to make it a complete sql ?

CREATE TRIGGER NyTrig NO CASCADE BEFORE INSERT ON MyTable
REFERENCING NEW as newdata FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE num_rows int;

SET num_rows = (select count(*) from MyTable as mt where (mt.col1 = newdata.col1 and mt.col2 = newdata.col2) );

if (num_rows > 0)
THEN SIGNAL SQLSTATE '75001' set message_text = ' There cannot be two entries on the same day';
END IF;

END


Although, in may opinion, a unique constraint / index is the better way to go.

Andy

A trigger is pretty much the worst choice. A unique constraint is supported by an index. With the index, a check for duplicates is trivial and done with a few page access only. With a trigger, you potentially have to read all data pages.

As the others have sad, index is the best choice for performance reasons.

In case you have to go the trigger route, a variation of the trigger stmt: ( sorry, didn't test it)