Trigger to check for duplicates

Question

I am writing a trigger and I have some problem. The trigger executes and compiles without errors but for some reasons it doesn't make the job I want to. If someone could help me.

Here is the question:

Write a trigger to be executed on insert, update of a PROPERTY. Every property that gets entered gets checked against other properties of having the same: Agent(s), Owner(s), Address, if you find one then update Property Status to “Double” as a duplicate.

I am inserting the same data and it let me do so, but it wouldn't normally!

Here are my tables:

create table Properties(  
            idProperties number(10) NOT NULL,
        Type varchar2(45) NOT NULL,
        SquareMeters varchar2(10) NOT NULL,
        Rooms number(10) NOT NULL,
        ConstructionDate date NOT NULL,
        FloorLocation varchar(20),
        Price number(10) NOT NULL,
        CityView varchar2(20),
        DateOfInsert date NOT NULL,
        DateOfExiration date NOT NULL,
        Address_FK number(20),
        Service_FK number(20),
        Ownership_FK number(20),
        Status_FK number(20),
        PropertyService_FK number(20))

create table Address(
          idAddress number(10) NOT NULL,
          address_name varchar2(20),
          City_FK number(20))


create table OwnerAgent(
           idOwnerAgent number(10) NOT NULL,
           Name varchar2(50)  NOT NULL,
           LastName varchar2(50)  NOT NULL,
           PhoneNr number(20),  
           Email varchar2(20),
           Sex varchar2(10),
           Profesion varchar2(20),
           Birthdate date,
           LastLogInDate date NOT NULL,
           Status varchar2(20),
           Address_FK number(20))

create table Ownership(
            idOwnership number(10) NOT NULL,
            PercentageOwed number(10)NOT NULL,
            RequiredPercentage number(10) NOT NULL,
        OwnerAgent_FK number(20))

Here is my trigger:

CREATE OR REPLACE TRIGGER Check_Duplicate
before insert or update on properties
FOR each ROW

declare
v_dup number;

begin
    select count(idProperties) INTO v_dup from properties where Address_FK=:NEW.Address_FK and 
     Ownership_FK=:NEW.Ownership_FK;

 if v_dup > 0 then
   Raise_Application_Error (-20100, 'This property already exists. The insert is cancelled.');
end if;
end;

Thank you.

Solution 1

You cannot, in general, enforce this sort of constraint in a trigger. You would need to use a constraint.

The problem you'll face if you try to use a trigger is that you'll generally encounter a "mutating table" exception. In general, a row-level trigger on table A (i.e. properties) cannot query table A. You can work around that problem by creating a package, creating a collection in that package, initializing the collection in a before statement trigger, writing the keys that are inserted or updated into the collection in a row-level trigger, and then iterating through the elements of the collection in an after statement trigger and issuing appropriate DML against the table. This, however, involves a whole lot of moving pieces and a whole lot of complexity (though the complexity is reduced if you're on 11g and can use a compound trigger instead).

Additionally, if you try to use a trigger, you'll encounter issues in multi-user environments. If user A inserts a row in one session and user B inserts a duplicate row in a different session before user A commits, neither session's trigger will detect the duplicate row. You can potentially work around this sort of problem by explicitly locking a row in the parent table in order to serialize inserts into the table (intentionally making the application slower and less scalable). But a constraint would be a much more efficient and practical solution.

All that being said, if you do only single-row inserts using the INSERT ... VALUES syntax and restrict yourself to a single session, your trigger does appear to work

SQL> ed
Wrote file afiedt.buf

  1  create table Properties(
  2          idProperties number(10) NOT NULL,
  3          Address_FK number(20),
  4          Ownership_FK number(20)
  5* )
SQL> /

Table created.

SQL> CREATE OR REPLACE TRIGGER Check_Duplicate
  2  before insert or update on properties
  3  FOR each ROW
  4
  5  declare
  6  v_dup number;
  7
  8  begin
  9      select count(idProperties) INTO v_dup from properties where Address_FK=
:NEW.Address_FK and
 10       Ownership_FK=:NEW.Ownership_FK;
 11
 12   if v_dup > 0 then
 13     Raise_Application_Error (-20100, 'This property already exists. The inse
rt is cancelled.');
 14  end if;
 15  end;
 16  /

Trigger created.

SQL> insert into properties values( 1, 10, 100 );

1 row created.

SQL> insert into properties values( 2, 10, 100 );
insert into properties values( 2, 10, 100 )
            *
ERROR at line 1:
ORA-20100: This property already exists. The insert is cancelled.
ORA-06512: at "SCOTT.CHECK_DUPLICATE", line 9
ORA-04088: error during execution of trigger 'SCOTT.CHECK_DUPLICATE'

Solution 2

Assignment says to change status to double if duplicate, not prevent it

CREATE OR REPLACE TRIGGER Check_Duplicate
before insert or update on properties
FOR each ROW

declare
v_dup number;

begin
    select count(idProperties) INTO v_dup from properties where Address_FK=:NEW.Address_FK and 
     Ownership_FK=:NEW.Ownership_FK;

 if v_dup > 0 then
   :New.Status :='DOUBLE'
end if;
end;

Comments

[Illyricum]

first of all thant for the explanation, and yes I am having problem with the mutation now. When I am trying to insert new data it says: ORA-04091: table YLLKA.PROPERTIES is mutating, trigger/function may not see it ORA-06512: at "YLLKA.CHECK_DUPLICATE", line 4 ORA-04088: error during execution of trigger 'YLLKA.CHECK_DUPLICATE' ORA-06512: at "YLLKA.DATES", line 4 ORA-04088: error during execution of trigger 'YLLKA.DATES'

[Justin Cave]

@Illyricum N. - Yes, that's expected. As I said, you can work around the mutating trigger exception using multiple triggers, a package, and a collection (or with a compound trigger in 11g) but that increases the complexity of the solution dramatically. Since a constraint is the proper way to implement this, I would be very hesitant to go too far down the path of defining these multiple triggers.

[Illyricum]

I will not understand going to far too. But how can I make this work? I am tired of this even though I started to like triggers but they cause lot of problems and make you nervous.

[Illyricum]

Justin, I make it work I just disabled the other trigger that I created, because I am using toad. thx a lot for your explanation. It helped me a lot. I have one more short question if you don't mind?

[Justin Cave]

@Illyricum N. - Be aware that you'll be right back to getting mutating trigger exceptions if you do an INSERT ... SELECT on your table rather than an INSERT ... VALUES in which case you'd need the three trigger solution or you'd need to enforce this properly with a constraint. And you still have the problem that the trigger doesn't handle multi-user systems.

[Illyricum]

Yes I understand, but I haven't gone that far yet. I am sure I'll be back here in the future for help. I am very happy that I found this nice site and nice people who are very helpful. Thx a lot for your time.