Stop executing trigger [message #297440] |
Thu, 31 January 2008 10:36  |
mestoganesh
Messages: 16 Registered: May 2007 Location: Chennai
|
Junior Member |
|
|
hi experts,
I have the following trigger to avoid the duplication entry.
this is existing table so i cant apply combination unique constraints.i have to avoid future duplicate entry.so i write a trigger.But it couldn't avoid(delete). please let me know any body know the answer.
CREATE OR REPLACE TRIGGER AVOID_DUP_PROJECTRATE
AFTER INSERT OR UPDATE
ON TEST1
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
RCOUNT NUMBER(1) ;
BEGIN
SELECT COUNT(*) INTO RCOUNT FROM TEST1
WHERE PROJECTID=:NEW.PROJECTID AND ACTIVITYCODEID=:NEW.ACTIVITYCODEID;
IF(RCOUNT>0)THEN
IF(INSERTING) THEN
DBMS_OUTPUT.PUT_LINE('CHECK-1 RCOUNT :'||RCOUNT);
DELETE FROM TEST1 WHERE ID=:NEW.ID;
COMMIT;
END IF;
END IF;
END;
/
|
|
|
|
Re: Stop executing trigger [message #297465 is a reply to message #297442] |
Thu, 31 January 2008 19:56   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
I don't think that's entirely true. This approach will never work, but the concept - as bad as it is - is possible.
You COULD do it with an after-insert statement-level trigger.
In an after-insert for each row trigger, save the ROWIDs of the inserted rows into a nested table that is declared in the specification of a package - or you could use a Global Temporary Table.
In an after-insert statement level trigger, the ROWIDs in the nested table or GTT will still be present. You loop through the rows, taking whatever action you deem neccessary (deleting them, in this case).
It is a revolting implementation and will gain you a reputation amongst all that see it as a dangerous liability to your department. The reason I mention it is because you are already on that path and would discover it eventually. At least this way I can tell you how bad it is. You be the judge.
Ross Leishman
|
|
|
Re: Stop executing trigger [message #297643 is a reply to message #297440] |
Fri, 01 February 2008 22:18   |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
REVOLTING... what a fine word.
Still, this is a not un-common requirement. My wife was faced with the same need many years ago: "no Mrs. Meade, you can't fix the dups that are already there, but you must make sure no more get into the table". There were no triggers in those days, so it was done via application logic and it was ugly no matter how you sliced it.
I offer another alternative, using a "key preserving table" so to speak. Here is example code.
Ross, I would like your comments on this one as you have a keen eye for the obscure when it comes to oracle details.
create table test1 (a number);
insert into test1 values (1);
insert into test1 values (1);
insert into test1 values (2);
insert into test1 values (3);
commit;
create table test1_nomoredups
as
select distinct a
from test1
/
alter table test1_nomoredups add primary key (a)
/
create or replace trigger aiup_test1
after insert or update or delete on test1
for each row
begin
if inserting then
insert into test1_nomoredups values (:new.a);
elsif updating('a') then
update test1_nomoredups set a = :new.a where a = :old.a;
else
delete from test1_nomoredups where a = :old.a;
end if;
end;
/
show errors
insert into test1 values (1);
insert into test1 values (2);
insert into test1 values (3);
insert into test1 values (4);
insert into test1 values (4);
update test1 set a = 5 where a = 1;
update test1 set a = 5 where a = 2;
delete from test1 where a = 1;
insert into test1 values (1);
The basic idea as you can see, is creating a second table that is the primary key that you should have had, populating this table with unique values from your real table, and then using a unique constraint on this "key preserving" table to stop introduction of duplicates. Some might think this at least a little better than a statement level trigger because it uses the "natural" constraint mechanism of the database to enforce uniqueness which is what you should have been doing in the first place, and only requires one trigger to do. Others might think it not as good because it involves duplicating data and using multiple tables which complicates other things like backups, recoveries, etc.
Falling in line with Ross et.al., I am not condoning your continued use of data that cannot clean itself up. But facts is facts, and when you are stuck in a situation where you have to do it, you need answers, not rebukes.
Good luck, YOU GONNA NEED IT, Kevin.
|
|
|
|
Re: Stop executing trigger [message #298060 is a reply to message #297648] |
Mon, 04 February 2008 20:27   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Hi Kevin,
I read through your solution, and it looks like the equivalent of a ENABLE NOVALIDATE unique constraint.
My understanding is that an INSERT on a duplicate key will return an error from the trigger. What I think the OP was looking for (maybe - who can tell?) was to SILENTLY discard the insert/update.
Although the OP wasn't specific about this, I applied Leishman's 1st law of badly designed applications, which states that:
Quote: | People not prepared to fix their data model are also not prepared to fix their application.
|
This being the (likely) case, they will not want an error to appear where once there was none. They just want data integrity to magically improve.
Ross Leishman
|
|
|
|