I have a before insert trigger that fires for each row on a detail table
(100, 1, AB123)
(100, 2, CD222)
(100, 3, DD111)
(200, 1, DD222)
(200, 2, D444)
(200, 3, D777)
The trigger checks if the new invoice number entered existed in the detail table and raise an error if it does.
The problem is that after the first row in the current transaction gets inserted the trigger for second row think the invoice number exists and raises the exception.
Is there a way to make it only fire once in the begining or end of transaction. I cant change the application code for this.
It has to something in he database.
I was thinking of statement level trigger but that would per statement and every insert for detail iten will be considered a statement.
I am wondering if i should create a new column for that sessionid and then do the count excluding the session id like
:new.session = session_id_for_transaction;
select count(*) from invoice_detail wheree....and sessionid <> 'sessionid_for_transaction'
Another option is to create a small master table for all existing invoice numbers and then check against that instead of detail table.
I am not sure if this will work.