触发检查重复 [英] Trigger to check for duplicates

问题描述

我正在编写触发器，但遇到了一些问题.触发器执行并编译没有错误，但是由于某些原因，它没有完成我想要的工作.如果有人可以帮助我.

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.

这是问题:

编写一个触发器，以在插入，更新属性时执行.输入的每个属性都将与其他具有相同属性的属性进行检查:代理，所有者，地址，如果找到一个，则将属性状态更新为双重".

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!

这是我的桌子:

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))

这是我的触发器:

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;

谢谢.

推荐答案

通常，您不能在触发器中实施这种约束.您将需要使用约束.

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

如果尝试使用触发器，您将面临的问题是您通常会遇到变异表"异常.通常，表A上的行级触发器(即properties)无法查询表A.您可以通过以下方式解决此问题:创建一个包，在该包中创建一个集合，在before语句触发器中初始化该集合，然后编写在行级触发器中插入或更新到集合中的键，然后在after语句触发器中遍历集合的元素，并针对该表发出适当的DML.但是，这涉及很多活动部件和很多复杂性(尽管如果使用11g，可以降低复杂性，而可以使用复合触发器).

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).

此外，如果尝试使用触发器，则在多用户环境中会遇到问题.如果用户A在一个用户会话中插入一行，而用户B在用户A提交之前在另一个会话中插入重复的行，则这两个会话的触发器都不会检测到该重复的行.您可以通过显式锁定父表中的一行以将插入序列化到表中(有意使应用程序变慢且可伸缩性降低)来潜在地解决此问题.但是约束将是一种更加有效和实用的解决方案.

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.

话虽如此，如果您仅使用INSERT ... VALUES语法执行单行插入并将自己限制为单个会话，则触发器确实可以工作

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'

这篇关于触发检查重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！