CREATE TRIGGER tr_update_alarmKey_message ON [dbo].[t_alarmKey_message]
FOR UPDATE
ASdeclare @bus_id varchar(20)
declare @isOp int
declare @message_no int
if update(isOp)
begin
select @message_no=message_no,@bus_id=bus_id,@isOp=isOp from inserted
if @isOp= 1
begin
if exists (select * from t_alarmkey_message where (message_no>@message_no) and (bus_id=@bus_id) and (isOp is null))
begin
set @isOp=0
end
else
begin
update t_bus set bkey=0,key_desc=Null where bus_id=@bus_id
end
end
end
对自身表怎么操作,如果对自身表UPDTE的话又怎么操作....
触发器是不是有大小限制?32KB?连注释吗?下面存储ALTER procedure [dbo].[gp_userReg] @userYHM varchar(8),
@intIsReg int out --输出参数 0:没有登录 1:已经登录
as
declare @strTempSql varchar(100)
if object_id('tempdb.dbo.##'+@userYHM) is null
begin
set @strTempSql = 'create table ##'+@userYHM+'(userYHM varchar(8))'
exec (@strTempSql)
set @intIsReg = 0
end
else
begin
set @intIsReg = 1
end
is
G_busid t_alarmKey_message.bus_id%type;
G_isop t_alarmKey_message.isop%type;
G_messageno t_alarmKey_message.message_no%type;
End Pkg_alarmkey_message_Update;
2:
create or replace TRIGGER TRG_Upd_alarmkey_message_aur after update --of isop
ON t_alarmKey_message
REFERENCING OLD AS "old" NEW AS "new"
FOR EACH ROW
begin
Pkg_alarmkey_message_Update.G_busid:=:new.bus_id;
Pkg_alarmkey_message_Update.G_messageno:=:new.message_no;
Pkg_alarmkey_message_Update.G_isop:=:new.isop;
end ;
3:
Create Or Replace Trigger TRG_Upd_alarmkey_message_aus
After update On t_alarmkey_message
declare
countnum number(4):=0;
Begin
if Pkg_alarmkey_message_Update.G_isop=1 then
select count(*) into countnum from t_alarmkey_message where (message_no>Pkg_alarmkey_message_Update.G_messageno) and (bus_id=Pkg_alarmkey_message_Update.G_busid) and (isOp is null);
if countnum>0 then
update t_bus set bkey=0,key_desc=Null where bus_id=Pkg_alarmkey_message_Update.G_busid;
end if;
end if;
end;