在 PL/SQL 触发器中使用 Select 语句的语法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/202609/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the syntax to use a Select statement inside a PL/SQL Trigger?
提问by Equistatic
This is what I currently have:
这是我目前拥有的：
CREATE OR REPLACE TRIGGER MYTRIGGER
AFTER INSERT ON SOMETABLE
FOR EACH ROW
DECLARE
v_emplid varchar2(10);
BEGIN
SELECT
personnum into v_emplid
FROM PERSON
WHERE PERSONID = :new.EMPLOYEEID;
dbms_output.put(v_emplid);
/* INSERT INTO SOMEOTHERTABLE USING v_emplid and some of the other values from the trigger table*/
END MYTRIGGER;
DBA_ERRORS has this error: PL/SQL: ORA-00923: FROM keyword not found where expected
DBA_ERRORS 有此错误：PL/SQL: ORA-00923: FROM 关键字未在预期位置找到
采纳答案by Justin Cave
1) There must be something else to your example because that sure seems to work for me
1）你的例子肯定还有别的东西，因为这似乎对我有用
SQL> create table someTable( employeeid number );
Table created.
SQL> create table person( personid number, personnum varchar2(10) );
Table created.
SQL> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER MYTRIGGER
2 AFTER INSERT ON SOMETABLE
3 FOR EACH ROW
4 DECLARE
5 v_emplid varchar2(10);
6 BEGIN
7 SELECT personnum
8 into v_emplid
9 FROM PERSON
10 WHERE PERSONID = :new.EMPLOYEEID;
11 dbms_output.put(v_emplid);
12 /* INSERT INTO SOMEOTHERTABLE USING v_emplid and some of the other values
from the trigger table*/
13* END MYTRIGGER;
14 /
Trigger created.
SQL> insert into person values( 1, '123' );
1 row created.
SQL> insert into sometable values( 1 );
1 row created.
2) You probably want to declare V_EMPLID as being of type Person.PersonNum%TYPE so that you can be certain that the data type is correct and so that if the data type of the table changes you won't need to change your code.
2) 您可能希望将 V_EMPLID 声明为 Person.PersonNum%TYPE 类型，以便您可以确定数据类型是正确的，并且如果表的数据类型发生更改，则无需更改代码。
3) I assume that you know that your trigger cannot query or update the table on which the trigger is defined (so no queries or inserts into someTable).
3）我假设您知道您的触发器无法查询或更新定义触发器的表（因此没有查询或插入 someTable）。
回答by Jason V
You are playing with Lava (not just fire) in your trigger. DBMS_OUTPUT in a trigger is really, really bad. You can blow-out on a buffer overflow in your trigger and the whole transaction is shot. Good luck tracking that down. If you must do output-to-console like behavior, invoke an AUTONOMOUS TRANSACTION procedure that writes to a table.
您在触发器中玩熔岩（不仅仅是火）。触发器中的 DBMS_OUTPUT 真的非常糟糕。您可以在触发器中的缓冲区溢出时爆发，并且整个事务都会被击中。祝你好运。如果您必须执行类似输出到控制台的行为，请调用一个写入表的 AUTONOMOUS TRANSACTION 过程。
Triggers are pretty evil. I used to like them, but they are too hard to remember about. They affect data often times leading to MUTATING data (scary and not just because Halloween is close).
触发器非常邪恶。我曾经喜欢它们，但它们太难记住了。它们经常影响数据，导致数据发生变异（可怕的不仅仅是因为万圣节快到了）。
We use triggers to change the value of columns like .new:LAST_MODIFIED := sysdate and .new:LAST_MODIFIED_BY := user. That's it.
我们使用触发器来更改 .new:LAST_MODIFIED := sysdate 和 .new:LAST_MODIFIED_BY := user 等列的值。就是这样。
Don't ever allow a TRIGGER to prevent a transaction from completing. Find another option.
永远不要让 TRIGGER 阻止交易完成。寻找另一个选择。
回答by HLGEM
I would not use a select statment in a trigger ever. Insert into the table rather than a select into. Once the table already exists select into does not work in most databases.
我永远不会在触发器中使用 select 语句。插入表而不是选择插入。一旦表已经存在 select into 在大多数数据库中不起作用。
