触发将行插入到日志表中的操作,该行将插入或删除已编译错误的人员表 [英] Trigger that inserts row into log table on insert or delete in a persons table compiled with errors
问题描述
我有一个带有名称和ID的人员表,以及一个具有who和what属性的日志表. 我想在删除或插入人员表时插入日志表.
I have a persons table with a name and id as well as a logs table with attributes who and what. I want to insert into the logs table when I delete or insert into the persons table.
这是我到目前为止所拥有的:
This is what I have so far:
CREATE OR REPLACE TRIGGER add_del
BEFORE INSERT OR DELETE ON persons
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO logs (who, what) VALUES (name, 'Insert into persons');
ELSE
INSERT INTO logs (who, what) VALUES (name, 'Delete from persons');
END IF;
END;
/
为什么会编译并显示错误:
Why does this compile with the error:
警告:触发器因编译错误而创建.
Warning: Trigger created with compilation errors.
推荐答案
由于您没有发布错误,因此我不得不猜测.我的猜测是问题在于name
在这种情况下不是有效的标识符.您需要引用:new.name
或:old.name
. :old.name
在插入时将是NULL
,而:new.name
在删除时将是NULL
,所以我假设您想要类似的东西
Since you didn't post the error, I have to guess. My guess is that the problem is that name
is not a valid identifier in this context. You need to reference either :new.name
or :old.name
. :old.name
will be NULL
on an insert while :new.name
will be NULL
on a delete so I'm assuming you want something like
CREATE OR REPLACE TRIGGER add_del
BEFORE INSERT OR DELETE ON persons
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO logs (who, what) VALUES (:new.name, 'Insert into persons');
ELSE
INSERT INTO logs (who, what) VALUES (:old.name, 'Delete from persons');
END IF;
END;
这篇关于触发将行插入到日志表中的操作,该行将插入或删除已编译错误的人员表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!