如何在oracle中创建触发器，该触发器将根据条件限制对表的插入和更新查询 [英] how to create a trigger in oracle which will restrict insertion and update queries on a table based on a condition

问题描述

我有这样的帐户表-

create table account
(
acct_id int,
cust_id int,
cust_name varchar(20)  
)

insert into account values(1,20,'Mark');
insert into account values(2,23,'Tom');
insert into account values(3,24,'Jim');

我想创建一个触发器，以确保acct_id为2且cust_id为23的帐户表中不能插入或更新任何记录.

I want to create a trigger which will ensure that no records can be inserted or update in account table having acct_id as 2 and cust_id as 23.

我的代码是-

create trigger tri_account
before insert or update
on account
for each row
begin
    IF (:new.acct_id == 2 and :new.cust_id == 23) THEN 
        DBMS_OUTPUT.PUT_LINE('No insertion with id 2 and 23.');
rollback;
    END IF; 
end;

因此创建了此触发器，但存在编译错误. 现在，当我插入acct_id为2且cust_id为23的任何记录时，它的确允许. 但我说错了 ORA-04098:触发器'OPS $ 0924769.TRI_ACCOUNT'无效且重新验证失败

so this trigger is created , but with compilation error. now when I insert any record with acct_id as 2 and cust_id as 23,it doesent allow. But I get an error saying ORA-04098: trigger 'OPS$0924769.TRI_ACCOUNT' is invalid and failed re-validation

我不明白.我还想显示一条消息，表明无法插入. 请帮助...

I don't understand this.I also want to show a message that dis insertion is not possible. please Help...

推荐答案

1. Oracle中的相等运算符为=，而不是==.
2. 您不能在触发器中提交或回滚.您可以引发一个异常，该异常导致触发语句失败并被回滚(尽管不一定必须回滚现有事务).
3. 创建该触发器时，似乎没有成功编译该触发器.如果使用的是SQL * Plus，则可以在创建PL/SQL对象后键入show errors来查看编译错误.
4. 您永远不应编写依赖于调用方能够查看来自DBMS_OUTPUT的输出的代码.大多数应用程序不会这样，如果触发器简单地尝试写入DBMS_OUTPUT缓冲区，大多数应用程序将不会知道DML操作失败.

1. The equality operator in Oracle is =, not ==.
2. You cannot commit or rollback in a trigger. You can throw an exception which causes the triggering statement to fail and to be rolled back (though the existing transaction will not necessarily be rolled back).
3. It does not appear that this trigger compiled successfully when you created it. If you are using SQL*Plus, you can type show errors after creating a PL/SQL object to see the compilation errors.
4. You should never write code that depends on the caller being able to see the output from DBMS_OUTPUT. Most applications will not so most applications would have no idea that the DML operation failed if your trigger simply tries to write to the DBMS_OUTPUT buffer.

将这些项目放在一起，您可以编写类似的内容

Putting those items together, you can write something like

create trigger tri_account
before insert or update
on account
for each row
begin
    IF (:new.acct_id = 2 and :new.cust_id = 23) THEN 
        raise_application_error( -20001, 'No insertion with id 2 and 23.');
    END IF; 
end;

这篇关于如何在oracle中创建触发器，该触发器将根据条件限制对表的插入和更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！