下面的代码是用Informix写的:
先定义一个存储过程
create procedure pu_mm12(poopercd like tmm12_operators.opercd,
pnopercd like tmm12_operators.opercd,
pooperator like tmm12_operators.operator,
pnoperator like tmm12_operators.operator,
poleader like tmm12_operators.leader,
pnleader like tmm12_operators.leader) define cnt int;
define error_code1 integer;
define error_code3 integer;
define error_code4 integer;
define error_code5 integer; begin
let error_code1=1;
let error_code3=3;
let error_code4=4;
let error_code5=5;
if pnopercd<>poopercd then
raise exception error_code3;
end if;
select count(*) into cnt from tmm12_operators
where leader='N' and opercd=pnoperator;
if pnoperator<>pooperator and cnt<1 then
raise exception error_code1;
end if;
if pnleader<>poleader and pnleader='Y' then
raise exception error_code4;
end if;
if pnleader<>poleader and pnleader='N' then
raise exception error_code5;
end if;
end;
end procedure
然后,用触发器调用
create trigger "informix".tu_mm12 update of opercd,operator,leader on "informix".tmm12_operators referencing old as pre new as post for each row
(
execute procedure "informix".pu_mm12(pre.opercd ,post.opercd ,pre.operator ,post.operator ,pre.leader ,post.leader )
);
===================
代码比较简单,相信大虾们也能猜出来其中的用法
===================
问题如下:
将这个存储和触发器联合使用改成SQL Server语法?我现在对SQL Server不熟悉,事多,还请大家帮忙解决下,马上给分!
先定义一个存储过程
create procedure pu_mm12(poopercd like tmm12_operators.opercd,
pnopercd like tmm12_operators.opercd,
pooperator like tmm12_operators.operator,
pnoperator like tmm12_operators.operator,
poleader like tmm12_operators.leader,
pnleader like tmm12_operators.leader) define cnt int;
define error_code1 integer;
define error_code3 integer;
define error_code4 integer;
define error_code5 integer; begin
let error_code1=1;
let error_code3=3;
let error_code4=4;
let error_code5=5;
if pnopercd<>poopercd then
raise exception error_code3;
end if;
select count(*) into cnt from tmm12_operators
where leader='N' and opercd=pnoperator;
if pnoperator<>pooperator and cnt<1 then
raise exception error_code1;
end if;
if pnleader<>poleader and pnleader='Y' then
raise exception error_code4;
end if;
if pnleader<>poleader and pnleader='N' then
raise exception error_code5;
end if;
end;
end procedure
然后,用触发器调用
create trigger "informix".tu_mm12 update of opercd,operator,leader on "informix".tmm12_operators referencing old as pre new as post for each row
(
execute procedure "informix".pu_mm12(pre.opercd ,post.opercd ,pre.operator ,post.operator ,pre.leader ,post.leader )
);
===================
代码比较简单,相信大虾们也能猜出来其中的用法
===================
问题如下:
将这个存储和触发器联合使用改成SQL Server语法?我现在对SQL Server不熟悉,事多,还请大家帮忙解决下,马上给分!
解决方案 »
- 不允许对具有不止一条 SELECT 语句的远程存储过程或存储过程使用服务器游标。
- 一个sum问题,为什么会转换成int出错!
- 如何遍历一个存贮在数据库中一个树状结构所有的结点
- 怎么知道电脑中有没有安装SQL SERVER?
- 怎么获取记录的插入时间(数据库中没有插入的时间列)
- 请教一个列转行更新的问题
- 急!关于在ASP中执行存储过程的问题---高手请进!!!
- 关于SQL SERVER的数据定义的问题
- 使用DBCC 检测数据库时出现以下提示
- uniqueidentifier类型字段,在trigger的inserted中select出来后默认转换为了smallint类型
- sql安装后在不同类型帐户中运行的问题。
- 存储过程返回值,此值是记录数值.由于表名是变量条件也是变量.正解是什么?
execute procedure "informix".pu_mm12(pre.opercd ,post.opercd ,pre.operator ,post.operator ,pre.leader ,post.leader )比如:
create trigger tu_mm12 on tmm12_operators for update
as
declare @a1 char(3)
declare @a2 char(3)
declare @a3 char(3)
declare @a4 char(3)
declare @a5 char(3)
declare @a6 char(3)
--这个地方如何写
......... execute procedure pu_mm12(@a1,@a2,@a3,@a4,@a5,@a6)go
select @a1 = opercd ,@a2 = .. ,@a3 = ... from inserted
是sql 语法吗?
这个表里面放的是刚刚更新的记录
那些变量自然也就这里副职了。
create proc pu_mm12(
@poopercd varchar(20),
@pnopercd varchar(20),
@pnoperator varchar(20),
@poleader varchar(20),
@pnleader varchar(20),
@op int output
)
as
begin
declare @cnt int
set @op = 0
if @pnopercd<>@poopercd
set @error_code = 3
select @cnt=count(*) from tmm12_operators where leader='N' and opercd=@pnoperator
if (@pnoperator<>@pooperator and @cnt<1)
set @error_code = 1
if (@pnleader<>@poleader and @pnleader='Y')
set @error_code = 4
if (@pnleader<>@poleader and @pnleader='N')
set @error_code = 5
end--也可用函数来代替
create function uf_mm12(
@poopercd varchar(20),
@pnopercd varchar(20),
@pnoperator varchar(20),
@poleader varchar(20),
@pnleader varchar(20)
)
returns int
as
begin
declare @error_code int
if @pnopercd<>@poopercd
set @error_code = 3
select @cnt=count(*) from tmm12_operators where leader='N' and opercd=@pnoperator
if (@pnoperator<>@pooperator and @cnt<1)
set @error_code = 1
if (@pnleader<>@poleader and @pnleader='Y')
set @error_code = 4
if (@pnleader<>@poleader and @pnleader='N')
set @error_code = 5
return isnull(@error_code,0)
end
create trigger informix on tu_mm12
for update
declare @opercd varchar(20)
declare @opercd varchar(20)
declare @operator varchar(20)
declare @operator varchar(20)
declare @leader varchar(20)
declare @leader varchar(20)
declare @op int
if update(opercd) or update(operator) or update(leader)
begin
declare cur_tmp cursor for
select opercd,opercd,operator,operator,leader,leader
from inserted
open cur_tmp
fetch next from cur_tmp into @opercd,@opercd,@operator,@operator,@leader,@leader
while @@fetch_status=0
begin
exec pu_mm12 @opercd,@opercd,@operator,@operator,@leader,@leader,@op output
--用函数
--select @op=dbo.uf_mm12(@opercd,@opercd,@operator,@operator,@leader,@leader)
fetch next from cur_tmp into @opercd,@opercd,@operator,@operator,@leader,@leader
end
close cur_tmp
deallocate cur_tmpend
======================================================
大家先,能看懂这句就可以了
referencing old as pre new as post for each row就是如何传参数问题,我刚才查了下在SQL SERVER中的有两张幻表inserted、deleted,
我想inserted等价于old, delete4等价于postfor each row 的意思是针对每行进行触发!
-----------------------------------------------------------------------
上面的已经是实现功能的最简洁的代码了,呵呵!
祝楼主早日解决问题.回家打游戏去喽!!