Home » SQL & PL/SQL » SQL & PL/SQL » Stop executing trigger (oracle 10g)
Stop executing trigger [message #297440] Thu, 31 January 2008 10:36 Go to next message
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 #297442 is a reply to message #297440] Thu, 31 January 2008 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This will never work unless you lock the table.

Oracle is not single user database.

If you have complex thing to do, use PL/SQL procedure not a SQL statement with triggers doing side effects.

I repeat THIS WILL NEVER WORK.
Full stop.

Regards
Michel
Re: Stop executing trigger [message #297465 is a reply to message #297442] Thu, 31 January 2008 19:56 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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 #297648 is a reply to message #297440] Fri, 01 February 2008 23:27 Go to previous messageGo to next message
mestoganesh
Messages: 16
Registered: May 2007
Location: Chennai
Junior Member
Thanks for reply experts,

I solved this problem in another way. that is i deleted the old rows instead of new rows and some little change in application.
Re: Stop executing trigger [message #298060 is a reply to message #297648] Mon, 04 February 2008 20:27 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Stop executing trigger [message #298218 is a reply to message #297440] Tue, 05 February 2008 06:36 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks for the analysis. Yes, enable/novalidate is a good description.

Kevin
Previous Topic: invalid LOB locator specified: ORA-22275 - using DBMS_LOB.LOADFROMFILE
Next Topic: Replace characters in a string using sql plus
Goto Forum:
  


Current Time: Fri Dec 09 06:03:22 CST 2016

Total time taken to generate the page: 0.10354 seconds