create or replace trigger tr_book_
after insert
on book_ for each rowdeclare
tmp_category varchar2(20);
tmp_smlcategory varchar2(20);
begin
select category into tmp_category from book where isbn = :new.isbn;
select smlcategory into tmp_smlcategory from book where isbn = :new.isbn; if inserting then
if tmp_category is not null
insert into category_ values(:new.,tmp_category); end if;
if tmp_smlcategory is not null
insert into small_category_ values(:new.,tmp_smlcategory);
end if;
end if; if deleting then
delete from category_ where category = tmp_category and = :old.;
delete from small_category_ where smlcategory = tmp_smlcategory and = :old.;
end if;
end;
本人初学oracle,不熟悉语法,请各位指教
解决方案 »
- Oracle能否实现GMT时间显示,形如 'Thu, 21 Apr 2011 12:32:06 GMT'
- 几个基础问题,还请多多指教!
- 高分求解PL/SQL语句中日期问题!!!!!!!!
- oracle游标循环问题。
- 一个存储过程的问题?
- 请教:我在安装oracle时,一直弹出“加载数据库时出错”的问题,无法安装,请帮忙,谢了
- 请求帮助-ORA-00604递归SQL层1出现错误
- kb835732 与oracle 816 是否冲突
- 我用PL/SQL DEVELOP把存储过程导出来了,但我不知道怎样可以把它导到数据库里,如果要拷贝到sql/plus里执行的话,文字太长了,要分很多次
- 请帮忙
- oracle中创建自动增长列错误
- 请教一个时间sql语句
AFTER INSERT OR DELETE ON book_
FOR EACH ROW
--触发器体中有对deleting判断
--所以加上or delete
DECLARE
tmp_category VARCHAR2(20);
tmp_smlcategory VARCHAR2(20);
BEGIN
--用一个语句就可以给两个变量赋值
SELECT category, smlcategory
INTO tmp_category, tmp_smlcategory
FROM book
WHERE isbn = :new.isbn; IF inserting THEN
IF tmp_category IS NOT NULL THEN --IF..THEN
INSERT INTO category_ VALUES (:new., tmp_category);
END IF;
IF tmp_smlcategory IS NOT NULL THEN --IF..THEN
INSERT INTO small_category_ VALUES (:new., tmp_smlcategory);
END IF;
END IF; IF deleting THEN
DELETE FROM category_
WHERE category = tmp_category
AND = :old.;
DELETE FROM small_category_
WHERE smlcategory = tmp_smlcategory
AND = :old.;
END IF;
END;