一直用的是函数与存储过程,触发器基本上没用。
昨天试写了个instead of 触发器,报:缺少befer,after, insert of关键字的错误
代码如下:create or replace trigger saleviewtr
on saleview instead of insert --出错的一行
declare
l_sale_id char(6);
begin
select sale_id
into l_sale_id
from inserted;
if substring(l_sale_id, 1, 3) = 'may' then
begin
insert into salemay
select sale_id, sale_name, sale_qua
from inserted;
end;
if substring(l_sale_id, 1, 3) = 'jun' then
begin
insert into salemay
select sale_id, sale_name, sale_qua
from inserted;
end
if substring(l_sale_id, 1, 3) = 'jul' then
begin
insert into salemay
select sale_id, sale_name, sale_qua
from inserted;
end;
end;
希望用过触发器的朋友帮忙解答下! 看其他地方还有错吗?
比如说:from inserted 对吗?
昨天试写了个instead of 触发器,报:缺少befer,after, insert of关键字的错误
代码如下:create or replace trigger saleviewtr
on saleview instead of insert --出错的一行
declare
l_sale_id char(6);
begin
select sale_id
into l_sale_id
from inserted;
if substring(l_sale_id, 1, 3) = 'may' then
begin
insert into salemay
select sale_id, sale_name, sale_qua
from inserted;
end;
if substring(l_sale_id, 1, 3) = 'jun' then
begin
insert into salemay
select sale_id, sale_name, sale_qua
from inserted;
end
if substring(l_sale_id, 1, 3) = 'jul' then
begin
insert into salemay
select sale_id, sale_name, sale_qua
from inserted;
end;
end;
希望用过触发器的朋友帮忙解答下! 看其他地方还有错吗?
比如说:from inserted 对吗?
变量声明错误,不能用DECLARE
写反了,先事件后ON基于的视图
instead of insert on saleview
create or replace trigger saleviewtr
instead of insert on saleview --修正
--declare --去掉declare
l_sale_id char(6);
begin
select sale_id
into l_sale_id
from inserted;
if substring(l_sale_id, 1, 3) = 'may' then
begin
insert into salemay
select sale_id, sale_name, sale_qua
from inserted;
end;
if substring(l_sale_id, 1, 3) = 'jun' then
begin
insert into salemay
select sale_id, sale_name, sale_qua
from inserted;
end
if substring(l_sale_id, 1, 3) = 'jul' then
begin
insert into salemay
select sale_id, sale_name, sale_qua
from inserted;
end;
end;
只适用于视图
不能指定before和after
不能在具有with check option选项的视图上建立
必须包含for each rowcreate or replace trigger saleviewtr
instead of insert on saleview
declare
l_sale_id char(6);
begin
select sale_id
into l_sale_id
from inserted;
if substring(l_sale_id, 1, 3) = 'may' then
begin
insert into salemay
select sale_id, sale_name, sale_qua
from inserted;
end;
if substring(l_sale_id, 1, 3) = 'jun' then
begin
insert into salemay
select sale_id, sale_name, sale_qua
from inserted;
end
if substring(l_sale_id, 1, 3) = 'jul' then
begin
insert into salemay
select sale_id, sale_name, sale_qua
from inserted;
end;
end;