sql 触发器问题

显示全部楼层 · 2011-5-4 20:30:39

/*编写触发器tri_book_update，当修改图书信息时，将被修改图书修改前和修改后的信息保存记录到book_Log表中（ book_Log表需自己创建）*/
CREATE TRIGGER tri_book_update
ON Book
FOR UPDATE
AS
INSERT INTO book_Log
SELECT SYSTEM_USER CURRENT_TIMESTAMP D.book_name,I.book_name,D.author,I.author,D.press,I.press,D.press_date,I.press_date,D.flag_borrow,I.flag_borrow,D.price,I.price FROM deleted D,inserted I
WHERE D.book_id=I.book_id;
哪错了？

千问 · 2011-5-4 20:30:39

CREATE TRIGGER tri_book_updateON BookFOR UPDATEAS INSERT INTO book_Log SELECT SYSTEM_USER ,CURRENT_TIMESTAMP, D.book_name,I.book_name,D.author,I.author,D.press,I.press,D.press_date,I.press_date,D.flag_borrow,I.flag_borrow,D.price,I.price FROM deleted D,inserted I WHERE D.book_id=I.book_id加逗号两个，去最后的分号，OK

千问 · 2011-5-4 20:30:39

你这个语句。。。同时删除和插入数据才会判定成功。。。。你需要分两步。一步将更新的数据取出保存。然后将原来的数据保存。

千问 · 2011-5-4 20:30:39

CREATE TRIGGER tri_book_updateON BookFOR UPDATEAS INSERT INTO book_Log SELECT SYSTEM_USER, CURRENT_TIMESTAMP, D.book_name,I.book_name,D.author,I.author,D.press,I.press,D.

千问 · 2011-5-4 20:30:39

CREATE TRIGGER tri_book_updateON BookFOR UPDATEAS好像是改成 INSERT INTO book_Log SELECT SYSTEM_USER CURRENT_TIMESTAMP D.book_name,I.book_name,D.author,I.author,D.press,I.pre

千问 · 2011-5-4 20:30:39

create or replace trigger tg_insertbefore insert on表名begin
dbms_output.put_line('insert trigger is chufa le .....');end;