Home » SQL & PL/SQL » SQL & PL/SQL » Preventing duplicate column value using Trigger (11g)
Preventing duplicate column value using Trigger [message #447874] Thu, 18 March 2010 05:06 Go to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
I am working on a assignment, How to prevent duplication with Trigger. I create following compound trigger for this, but it is not doing so. How i can improve this to get required result......
I know this can be done with Constraint and ........
But this is a assignment , please suggest solution through trigger.
CREATE OR REPLACE TRIGGER CHK_DUPLICATION FOR INSERT ON TST COMPOUND TRIGGER

R_BCOUNT NUMBER;
R_ACOUNT NUMBER;
D_VAL NUMBER;

BEFORE STATEMENT IS

BEGIN
R_BCOUNT :=0;

SELECT COUNT(*) INTO R_BCOUNT FROM TST ;

END BEFORE STATEMENT;
 
BEFORE EACH ROW IS
BEGIN
D_VAL :=0;
SELECT COUNT(*) INTO D_VAL FROM TST WHERE :NEW.C1 = :OLD.C1;
IF D_VAL > 0 THEN

RAISE_APPLICATION_ERROR (-20010,'Duplicate C1 Value '|| :NEW.C1);

END IF ;
END BEFORE EACH ROW;

AFTER EACH ROW IS
BEGIN
IF D_VAL > 0 THEN

RAISE_APPLICATION_ERROR (-20010,'Duplicate C1 Value '|| :NEW.C1);

END IF ;

END AFTER EACH ROW;


AFTER STATEMENT IS
BEGIN
SELECT COUNT(*) INTO R_ACOUNT FROM TST ;
DBMS_OUTPUT.PUT_LINE('Rec Count before insert  '||R_BCOUNT);
DBMS_OUTPUT.PUT_LINE('Rec Count After insert  '||R_ACOUNT);
END AFTER STATEMENT ;

END CHK_DUPLICATION;


[add code tags and remove LFs (JR)]

[Updated on: Thu, 18 March 2010 10:18] by Moderator

Report message to a moderator

Re: Preventing duplicate column value using Trigger [message #447878 is a reply to message #447874] Thu, 18 March 2010 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is unreadable.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: Preventing duplicate column value using Trigger [message #447885 is a reply to message #447874] Thu, 18 March 2010 05:55 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
scott@orcl> create table dummy (x int);

Table created.


scott@orcl> create or replace package dummypackage as
  2  type gnum_tbl is table of dummy.x%type index by pls_integer;
  3  pnum_Tbl gnum_tbl;
  4  pnum_empty gnum_tbl;
  5  end  dummypackage;
  6  /

Package created.


scott@orcl> create or replace trigger bef_inst_updt_dummy before insert
  2  or update on dummy
  3  begin
  4  lock table dummy in exclusive mode;
  5  dummypackage.pnum_tbl := dummypackage.pnum_empty;
  6  end;
  7  /

Trigger created.

scott@orcl> create or replace trigger bef_inst_updt_row_dummy before insert
  2  or update on dummy
  3  for each row
  4  begin
  5  dummypackage.pnum_Tbl(dummypackage.pnum_Tbl.count+1) := :new.x;
  6  end;
  7  /
Trigger created.


scott@orcl> create or replace trigger aft_inst_updt_dummy after insert
  2  or update on dummy
  3  declare
  4  lv_count int;
  5  begin
  6  for i in 1..dummypackage.pnum_Tbl.count
  7  loop
  8     select count(*) into lv_count from dummy
  9             where x = dummypackage.pnum_tbl(i);
 10     if lv_count > 1
 11     then
 12             raise_application_error(-20001,'Duplicate record');
 13     end if;
 14  end loop;
 15  end;
 16  /

Trigger created.


scott@orcl> insert into dummy values(10);

1 row created.

scott@orcl> insert into dummy values(10);
insert into dummy values(10)
            *
ERROR at line 1:
ORA-20001: Duplicate record
ORA-06512: at "SCOTT.AFT_INST_UPDT_DUMMY", line 10
ORA-04088: error during execution of trigger 'SCOTT.AFT_INST_UPDT_DUMMY'


scott@orcl> insert into dummy  select rownum r from dual connect by level <= 5;

5 rows created.

scott@orcl> insert into dummy  select rownum r from dual connect by level <= 5;
insert into dummy  select rownum r from dual connect by level <= 5
            *
ERROR at line 1:
ORA-20001: Duplicate record
ORA-06512: at "SCOTT.AFT_INST_UPDT_DUMMY", line 10
ORA-04088: error during execution of trigger 'SCOTT.AFT_INST_UPDT_DUMMY'


scott@orcl>

scott@orcl> commit;

Commit complete.

scott@orcl> select * from dummy;

         X
----------
        10
         1
         2
         3
         4
         5

6 rows selected.

scott@orcl>



However please note only correct way to identify duplicate reoords is by defining primary /unique constraint
Re: Preventing duplicate column value using Trigger [message #447967 is a reply to message #447874] Thu, 18 March 2010 13:59 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
As bonker has indicated, his trigger code is not a good solution. bonker has offered this solution to show you that it is possible to do what you want, not that you should do it. With bonker's permission I will add lib with some commentary on his code:

1) Notice the bonker has taken the step to lock the table in exclusive mode in a BEFORE INSERT trigger. This is the only way he can ensure that concurrent transactions do not both insert the same row during overlapping inserts. Kudos to bonker for doing this. This is one of the most common mistakes people make with triggers; not accounting for concurrent transactions using the trigger code at the same time and remembering that concurrent transactions cannot normally see each others work.

Consider that this lock effectively converts your mulit-user oracle system into a single user database. What an excellent use of your expensive database software eh.

2) Next notice the bonker is using the MUTATING/CONSTRAINING TABLE ERROR AVOIDANCE METHOD #1 (package variables) to avoid the problems of mutating and constraining tables. This is necessary because bonker in his solution must read the MUTATING TABLE (the table the trigger is defined on) while executing the trigger code. Of course there are issues with this method. One is that it requires the use of PACKAGE GLOBAL VARIABLES inside trigger code and this is bad.

Many people do not know that triggers often do silent rollback and restart while they are working. Yes, it is exactly as it sounds, a trigger can come to what it thinks is a correctable error, so it will silently issue a rollback, and restart itself. This is true for single triggers and trigger chains. The problem is that rollback does not reset package state (it can't, this would invalidate current session package state for the packages the trigger is using and thus raise a non-correctable error). Thus GLOBAL PACKAGE VARIABLES are not reset to their initial values whenever a rollback and restart of a trigger occurrs. If your trigger logic is dependent upon the values of GLOBAL PACKAGE VARIABLES then it is possible that the execution path inside your trigger can change with every restart and this is usually a very bad thing. Tom Ktye of ASKTOMHOME has an article and some posts about it for those who care to look for them. In bonker's case, his triggers will still work if a silent rollback/restart should happen, just inefficiently.

3) lastly, the solution is basically ugly. Who wants to scrounge around all these different code components to try and understand that what they mean. The necessary understanding is only possible when one looks at the three triggers together as a cooperative code set but most people won't do that because it requires them to actually use their brains and go beyond thinking about code as just a "one program at a time" kind of thing.

So in the end I would have these last comments:

1) the solution by bonker is junk and should not be used. bonker knows this because he pretty much said so himself.

2) if I was looking for a database plsql developer and bonker showed up at an interview with this piece of code, I would hire him on the spot. bonker has demonstrated three critical things from a technical perspective that make him highly desireable:

a) he knows bad code when he sees it

b) he shows a respect for data by enforcing the idea at the end of his code post that the only real way to do this is with constraints. Such data respect is lacking in the world of developers yet is key to people doing database work.

c) he demonstrates an advanced understanding of what it means to write trigger code in a mult-user/concurrent processing world and this is something most people refuse to put the effort out to learn. Although the solution he offers is crap, he shows that if forced to create such a piece of crap, he will at least write crap which is not quite as crappy as everybody else's crap because in the end, his crap will give the right answer now, without causing data corruption problems later on.

Good job bonker.

Kevin
Re: Preventing duplicate column value using Trigger [message #448010 is a reply to message #447967] Fri, 19 March 2010 01:40 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
@Kevin

Thanks a bunch for all your kind words about me. Smile

and Yes, I fully agree with all the points that you mention about the associated evil/issues with this type of bad implementation of data integrity codes/triggers.
Re: Preventing duplicate column value using Trigger [message #448016 is a reply to message #447885] Fri, 19 March 2010 02:10 Go to previous messageGo to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
I am thank full for your co-operation. I have been able to do this in following way , I created a view of that table

create or replace view v_t1 as select * from t1;

and in trigger i give following command


SELECT count(*) into d_val from v_t1 where :new.c1 = t1.c1;

if d_val > 0 then
raise_application_error.........
endif
.
..

It is doing what is required.
What you say is this is correct way ???

Re: Preventing duplicate column value using Trigger [message #448017 is a reply to message #447967] Fri, 19 March 2010 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Good job bonker.

Kevin


And even better comment, Kevin.

Regards
Michel
Re: Preventing duplicate column value using Trigger [message #448026 is a reply to message #448016] Fri, 19 March 2010 02:28 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
It is doing what is required.
What you say is this is correct way ???



No that will not be correct way. Try to run insert same value in 2 concurrent session and see what happens?

i.e in Session 1 . Insert into <<your table>> value (1);

and without committing this session, open another session and run the same insert...

in session 2 Insert into <<your table>> value (1);


Re: Preventing duplicate column value using Trigger [message #448059 is a reply to message #447885] Fri, 19 March 2010 05:10 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
Hi.....

I have seen the requirement and regarding the solution i have few questions,
i don't understand the necessecity of some line of code, can you please explain to me, i am very much eager to know this.

here i highlight those

scott@orcl> create table dummy (x int);

Table created.


scott@orcl> create or replace package dummypackage as
2 type gnum_tbl is table of dummy.x%type index by pls_integer;
3 pnum_Tbl gnum_tbl;
4 pnum_empty gnum_tbl; --What is the need for another Collection
5 end dummypackage;
6 /

Package created.


scott@orcl> create or replace trigger bef_inst_updt_dummy before insert
2 or update on dummy
3 begin
4 lock table dummy in exclusive mode;
5 dummypackage.pnum_tbl := dummypackage.pnum_empty; --Why is this step necessary
6 end;
7 /

Trigger created.

scott@orcl> create or replace trigger bef_inst_updt_row_dummy before insert
2 or update on dummy
3 for each row
4 begin
5 dummypackage.pnum_Tbl(dummypackage.pnum_Tbl.count+1) := :new.x;
6 end;
7 /
Trigger created.


scott@orcl> create or replace trigger aft_inst_updt_dummy after insert
2 or update on dummy
3 declare
4 lv_count int;
5 begin
6 for i in 1..dummypackage.pnum_Tbl.count
7 loop
8 select count(*) into lv_count from dummy
9 where x = dummypackage.pnum_tbl(i);
10 if lv_count > 1
11 then
12 raise_application_error(-20001,'Duplicate record');
13 end if;
14 end loop;
15 end;
16 /

Trigger created.


scott@orcl> insert into dummy values(10);

1 row created.

scott@orcl> insert into dummy values(10);
insert into dummy values(10)
*
ERROR at line 1:
ORA-20001: Duplicate record
ORA-06512: at "SCOTT.AFT_INST_UPDT_DUMMY", line 10
ORA-04088: error during execution of trigger 'SCOTT.AFT_INST_UPDT_DUMMY'


scott@orcl> insert into dummy select rownum r from dual connect by level <= 5;

5 rows created.

scott@orcl> insert into dummy select rownum r from dual connect by level <= 5;
insert into dummy select rownum r from dual connect by level <= 5
*
ERROR at line 1:
ORA-20001: Duplicate record
ORA-06512: at "SCOTT.AFT_INST_UPDT_DUMMY", line 10
ORA-04088: error during execution of trigger 'SCOTT.AFT_INST_UPDT_DUMMY'


scott@orcl>

scott@orcl> commit;

Commit complete.

scott@orcl> select * from dummy;

X
----------
10
1
2
3
4
5

6 rows selected.

scott@orcl>

Regards,
Suvro

[Updated on: Fri, 19 March 2010 05:19]

Report message to a moderator

Re: Preventing duplicate column value using Trigger [message #448063 is a reply to message #448059] Fri, 19 March 2010 05:34 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
dummypackage.pnum_tbl := dummypackage.pnum_empty; --Why is this step necessary


I want to ensure that before insert or update begins, my package variable is set to empty.

If I do not do this then:

1) My collection keeps on increasing un-neccessarily if I Insert/Update more records in same session but during differen time and
2) Kevin has already pointed another reason for this

Many people do not know that triggers often do silent rollback and restart while they are working. Yes, it is exactly as it sounds, a trigger can come to what it thinks is a correctable error, so it will silently issue a rollback, and restart itself. This is true for single triggers and trigger chains. The problem is that rollback does not reset package state (it can't, this would invalidate current session package state for the packages the trigger is using and thus raise a non-correctable error). Thus GLOBAL PACKAGE VARIABLES are not reset to their initial values whenever a rollback and restart of a trigger occurrs. If your trigger logic is dependent upon the values of GLOBAL PACKAGE VARIABLES then it is possible that the execution path inside your trigger can change with every restart and this is usually a very bad thing

Now I could have set it empty by using dummypackage.pnum_tbl.delete but I think (but am not sure) setting it to an empty collection is an efficient way to do this.

Hope this helps.

[Updated on: Fri, 19 March 2010 05:38]

Report message to a moderator

Re: Preventing duplicate column value using Trigger [message #448067 is a reply to message #448063] Fri, 19 March 2010 05:52 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
Thank you to making the things clear, now is understand the need to empty the Package Variable, this will definitely help.

But i have a few questions regarding what Mr. Kevin has written about "restart of a trigger".

Thanks for the Help.
Regards,
Suvro
Re: Preventing duplicate column value using Trigger [message #448069 is a reply to message #447967] Fri, 19 March 2010 06:23 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
Hi..

I have an question regarding this

Quote:
Many people do not know that triggers often do silent rollback and restart while they are working.


Can you give me an explanation about "restart of triggers". I am not been able to understand this part.

for a row level trigger the trigger code executes every time a row is effected.. do you mean this?

Regards,
Suvro
Re: Preventing duplicate column value using Trigger [message #448081 is a reply to message #448069] Fri, 19 March 2010 07:07 Go to previous message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
Can you give me an explanation about "restart of triggers". I am not been able to understand this part.


Tom Kyte's blog - Part I
Tom Kyte's blog - Part II
Tom Kyte's blog - Part III
Previous Topic: decision tree in sql
Next Topic: DB link creating problem.
Goto Forum:
  


Current Time: Fri Sep 30 03:48:39 CDT 2016

Total time taken to generate the page: 0.11234 seconds