......Select @RowsD=Count(*) From Deleted Select @RowsI=Count(*) From Inserted If @RowsD>0 And @RowsI=0 Set @Dml=3......if @DML=3 BEGIN select @n_id =id from deleted --得到ID delete from report_01 where source_id=cast(@n_id as varchar(10)) END
新手,见谅! CREATE TRIGGER write_report01 ON [dbo].[zcjgmxb] FOR INSERT, UPDATE, DELETE AS declare @s_hsdw varchar(10), --原表的核算单位 @n_id numeric(9), --原表的ID @d_kjrq datetime, --原表的记账日期 @s_zffs varchar(2), --原表的支付方式 @s_jjyt varchar(2), --原表的经济用途 @m_je money , --原表的支付金额 @s_import_pc varchar(10) , --导入的批次,如果是0000表示手工录入的账 @m_zjzf money , --报表的直接直付 @m_sqzf money , --报表的授权支付 @m_zhzf money , --报表的专户支付 @m_jjyt01 money ,--报表的经济用途 @m_jjyt02 money ,--报表的经济用途 @m_jjyt03 money ,--报表的经济用途 @m_jjyt04 money ,--报表的经济用途 @m_jjyt05 money ,--报表的经济用途 @m_jjyt06 money --报表的经济用途set @m_zjzf=0 set @m_sqzf=0 set @m_zhzf=0 set @m_jjyt01=0 set @m_jjyt02=0 set @m_jjyt03=0 set @m_jjyt04=0 set @m_jjyt05=0 set @m_jjyt06=0Declare @Dml TinyInt --1:Insert 2:Update 3:Delete Declare @RowsD Int Declare @RowsI Int--确定是哪一种dml操作 Select @RowsD=Count(*) From Deleted Select @RowsI=Count(*) From Inserted If @RowsD=0 And @RowsI=0 return
If @RowsD=0 And @RowsI>0 Set @Dml=1 Else If @RowsD>0 And @RowsI>0 Set @Dml=2 Else If @RowsD>0 And @RowsI=0 Set @Dml=3 -- 变量说明 @s_hsdw varchar(10), --原表的核算单位 -- @n_id numeric(9), --原表的ID -- @d_kjrq datetime, --原表的记账日期 -- @s_zffs varchar(2), --原表的支付方式 -- @m_je money --原表的支付金额 IF @DML=1 or @DML=2 --如果是插入或更新操作, begin -- select @s_hsdw=chsdw01 From inserted --得到核算单位 select @n_id =id from inserted --得到ID select @d_kjrq =dzcrq from inserted --得到支出日期 select @s_zffs =szffs01 from inserted --得到支付方式 select @s_jjyt=stzxt_jjyt01 from inserted --原表的经济用途 select @m_je =mje from inserted --得到支付金额 select @s_import_pc=import_pc from inserted --导入的批次-- 分解支付方式 01直接,02授权 03专户 if @s_zffs='01' set @m_zjzf=@m_je else if @s_zffs='02' set @m_sqzf=@m_je else if @s_zffs='03' set @m_zhzf=@m_je --分解经济用济if @s_jjyt='01' set @m_jjyt01=@m_je else if @s_jjyt='02' set @m_jjyt02=@m_je else if @s_jjyt='03' set @m_jjyt03=@m_je else if @s_jjyt='04' set @m_jjyt04=@m_je else if @s_jjyt='05' set @m_jjyt05=@m_je else if @s_jjyt='06' set @m_jjyt06=@m_je
if @DML=1 begin if @s_import_pc='0000' insert into report_01(source_id,hsdw,kjrq,zjzf_je,sqzf_je,zhzf_je,jjyt01_je,jjyt02_je,jjyt03_je,jjyt04_je,jjyt05_je,jjyt06_je ) values (@n_id,@s_hsdw,@d_kjrq,@m_zjzf,@m_sqzf,@m_zhzf,@m_jjyt01,@m_jjyt02,@m_jjyt03,@m_jjyt04,@m_jjyt05,@m_jjyt06) else insert into report_01(source_id,hsdw,kjrq,zjzf_je,sqzf_je,zhzf_je,jjyt01_je,jjyt02_je,jjyt03_je,jjyt04_je,jjyt05_je,jjyt06_je ) values (@n_id,@s_hsdw,@d_kjrq,@m_zjzf,@m_sqzf,0,@m_jjyt01,@m_jjyt02,@m_jjyt03,@m_jjyt04,@m_jjyt05,@m_jjyt06) end if @DML=2 update report_01 set zjzf_je=@m_zjzf,sqzf_je=@m_sqzf,zhzf_je=@m_zhzf,jjyt01_je=@m_jjyt01,jjyt02_je=@m_jjyt02,jjyt03_je=@m_jjyt03,jjyt04_je=@m_jjyt04,jjyt05_je=@m_jjyt05,jjyt06_je=@m_jjyt06 where source_id=cast(@n_id as varchar(10))ENDif @DML=3 BEGIN select @n_id =id from deleted --得到ID delete from report_01 where source_id=cast(@n_id as varchar(10)) END
select @s_hsdw = chsdw01,@n_id = id,@d_kjrq = dzcrq, @s_zffs =szffs01,@s_jjyt=stzxt_jjyt01, @m_je = mje,@s_import_pc=import_pc From inserted --得到所有变量值if @s_zffs = '01' set @m_zjzf=@m_je else if @s_zffs='02' set @m_sqzf=@m_je else if @s_zffs='03' set @m_zhzf=@m_je --分解经济用济if @s_jjyt='01' set @m_jjyt01=@m_je else if @s_jjyt='02' set @m_jjyt02=@m_je else if @s_jjyt='03' set @m_jjyt03=@m_je else if @s_jjyt='04' set @m_jjyt04=@m_je else if @s_jjyt='05' set @m_jjyt05=@m_je else if @s_jjyt='06' set @m_jjyt06 = @m_jeif exists(select 1 from inserted) and not exists(select 1 from deleted)--新增 begin
if @s_import_pc='0000' begin insert into report_01(source_id,hsdw,kjrq,zjzf_je,sqzf_je,zhzf_je,jjyt01_je,jjyt02_je, jjyt03_je,jjyt04_je,jjyt05_je,jjyt06_je ) values (@n_id,@s_hsdw,@d_kjrq,@m_zjzf,@m_sqzf,@m_zhzf,@m_jjyt01,@m_jjyt02,@m_jjyt03, @m_jjyt04,@m_jjyt05,@m_jjyt06) end else begin insert into report_01(source_id,hsdw,kjrq,zjzf_je,sqzf_je,zhzf_je,jjyt01_je,jjyt02_je, jjyt03_je,jjyt04_je,jjyt05_je,jjyt06_je ) values (@n_id,@s_hsdw,@d_kjrq,@m_zjzf,@m_sqzf,0,@m_jjyt01,@m_jjyt02,@m_jjyt03,@m_jjyt04, @m_jjyt05,@m_jjyt06) end end if exists(select 1 from inserted) and exists(select 1 from deleted) --修改 begin update report_01 set zjzf_je=@m_zjzf,sqzf_je=@m_sqzf,zhzf_je=@m_zhzf,jjyt01_je=@m_jjyt01, jjyt02_je=@m_jjyt02,jjyt03_je=@m_jjyt03,jjyt04_je=@m_jjyt04,jjyt05_je=@m_jjyt05,jjyt06_je =@m_jjyt06 where source_id=cast(@n_id as varchar(10)) end if not exists(select 1 from inserted) --删除 begin --select @n_id = id from deleted --得到ID多余的上面已赋值 delete from report_01 where source_id=cast(@n_id as varchar(10)) end END
还是不行,只删除了report_01表的最后一行
应该是你delete from report_01 where source_id=cast(@n_id as varchar(10)) 这句的问题。你看看你在SQL下是不是能正常删除这些ID的
游标循环删除 DECLARE CUR CURSOR FOR SELECT ID FROM DELETED OPEN CUR DECLARE @N_ID INT FETCH NEXT FROM CUR INTO @N_ID WHILE @@FETCH_STATUS = 0 BEGIN select @n_id =id from deleted --得到ID delete from report_01 where source_id=cast(@n_id as varchar(10)) FETCH NEXT FROM CUR INTO @N_ID END
如果有多表,表之间如何关联?
发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
Select @RowsI=Count(*) From Inserted
If @RowsD>0 And @RowsI=0
Set @Dml=3......if @DML=3
BEGIN
select @n_id =id from deleted --得到ID
delete from report_01 where source_id=cast(@n_id as varchar(10))
END
CREATE TRIGGER write_report01 ON [dbo].[zcjgmxb]
FOR INSERT, UPDATE, DELETE
AS
declare @s_hsdw varchar(10), --原表的核算单位
@n_id numeric(9), --原表的ID
@d_kjrq datetime, --原表的记账日期
@s_zffs varchar(2), --原表的支付方式
@s_jjyt varchar(2), --原表的经济用途
@m_je money , --原表的支付金额
@s_import_pc varchar(10) , --导入的批次,如果是0000表示手工录入的账
@m_zjzf money , --报表的直接直付
@m_sqzf money , --报表的授权支付
@m_zhzf money , --报表的专户支付
@m_jjyt01 money ,--报表的经济用途
@m_jjyt02 money ,--报表的经济用途
@m_jjyt03 money ,--报表的经济用途
@m_jjyt04 money ,--报表的经济用途
@m_jjyt05 money ,--报表的经济用途
@m_jjyt06 money --报表的经济用途set @m_zjzf=0
set @m_sqzf=0
set @m_zhzf=0
set @m_jjyt01=0
set @m_jjyt02=0
set @m_jjyt03=0
set @m_jjyt04=0
set @m_jjyt05=0
set @m_jjyt06=0Declare @Dml TinyInt --1:Insert 2:Update 3:Delete
Declare @RowsD Int
Declare @RowsI Int--确定是哪一种dml操作
Select @RowsD=Count(*) From Deleted
Select @RowsI=Count(*) From Inserted
If @RowsD=0 And @RowsI=0
return
If @RowsD=0 And @RowsI>0
Set @Dml=1
Else
If @RowsD>0 And @RowsI>0
Set @Dml=2
Else
If @RowsD>0 And @RowsI=0
Set @Dml=3
-- 变量说明 @s_hsdw varchar(10), --原表的核算单位
-- @n_id numeric(9), --原表的ID
-- @d_kjrq datetime, --原表的记账日期
-- @s_zffs varchar(2), --原表的支付方式
-- @m_je money --原表的支付金额
IF @DML=1 or @DML=2 --如果是插入或更新操作,
begin
--
select @s_hsdw=chsdw01 From inserted --得到核算单位
select @n_id =id from inserted --得到ID
select @d_kjrq =dzcrq from inserted --得到支出日期
select @s_zffs =szffs01 from inserted --得到支付方式
select @s_jjyt=stzxt_jjyt01 from inserted --原表的经济用途
select @m_je =mje from inserted --得到支付金额
select @s_import_pc=import_pc from inserted --导入的批次-- 分解支付方式 01直接,02授权 03专户
if @s_zffs='01'
set @m_zjzf=@m_je
else
if @s_zffs='02'
set @m_sqzf=@m_je
else
if @s_zffs='03'
set @m_zhzf=@m_je
--分解经济用济if @s_jjyt='01'
set @m_jjyt01=@m_je
else
if @s_jjyt='02'
set @m_jjyt02=@m_je
else
if @s_jjyt='03'
set @m_jjyt03=@m_je
else
if @s_jjyt='04'
set @m_jjyt04=@m_je
else
if @s_jjyt='05'
set @m_jjyt05=@m_je
else
if @s_jjyt='06'
set @m_jjyt06=@m_je
if @DML=1
begin
if @s_import_pc='0000'
insert into report_01(source_id,hsdw,kjrq,zjzf_je,sqzf_je,zhzf_je,jjyt01_je,jjyt02_je,jjyt03_je,jjyt04_je,jjyt05_je,jjyt06_je )
values (@n_id,@s_hsdw,@d_kjrq,@m_zjzf,@m_sqzf,@m_zhzf,@m_jjyt01,@m_jjyt02,@m_jjyt03,@m_jjyt04,@m_jjyt05,@m_jjyt06)
else
insert into report_01(source_id,hsdw,kjrq,zjzf_je,sqzf_je,zhzf_je,jjyt01_je,jjyt02_je,jjyt03_je,jjyt04_je,jjyt05_je,jjyt06_je )
values (@n_id,@s_hsdw,@d_kjrq,@m_zjzf,@m_sqzf,0,@m_jjyt01,@m_jjyt02,@m_jjyt03,@m_jjyt04,@m_jjyt05,@m_jjyt06)
end
if @DML=2
update report_01 set zjzf_je=@m_zjzf,sqzf_je=@m_sqzf,zhzf_je=@m_zhzf,jjyt01_je=@m_jjyt01,jjyt02_je=@m_jjyt02,jjyt03_je=@m_jjyt03,jjyt04_je=@m_jjyt04,jjyt05_je=@m_jjyt05,jjyt06_je=@m_jjyt06
where source_id=cast(@n_id as varchar(10))ENDif @DML=3
BEGIN
select @n_id =id from deleted --得到ID
delete from report_01 where source_id=cast(@n_id as varchar(10)) END
FOR INSERT, UPDATE, DELETE
ASbegin
declare @s_hsdw varchar(10), --原表的核算单位
@n_id numeric(9), --原表的ID
@d_kjrq datetime, --原表的记账日期
@s_zffs varchar(2), --原表的支付方式
@s_jjyt varchar(2), --原表的经济用途
@m_je money , --原表的支付金额
@s_import_pc varchar(10) , --导入的批次,如果是0000表示手工录入的账
@m_zjzf money , --报表的直接直付
@m_sqzf money , --报表的授权支付
@m_zhzf money , --报表的专户支付
@m_jjyt01 money ,--报表的经济用途
@m_jjyt02 money ,--报表的经济用途
@m_jjyt03 money ,--报表的经济用途
@m_jjyt04 money ,--报表的经济用途
@m_jjyt05 money ,--报表的经济用途
@m_jjyt06 money --报表的经济用途
select
@s_hsdw = chsdw01,@n_id = id,@d_kjrq = dzcrq,
@s_zffs =szffs01,@s_jjyt=stzxt_jjyt01,
@m_je = mje,@s_import_pc=import_pc
From inserted --得到所有变量值if @s_zffs = '01'
set @m_zjzf=@m_je
else if @s_zffs='02'
set @m_sqzf=@m_je
else if @s_zffs='03'
set @m_zhzf=@m_je
--分解经济用济if @s_jjyt='01'
set @m_jjyt01=@m_je
else if @s_jjyt='02'
set @m_jjyt02=@m_je
else if @s_jjyt='03'
set @m_jjyt03=@m_je
else if @s_jjyt='04'
set @m_jjyt04=@m_je
else if @s_jjyt='05'
set @m_jjyt05=@m_je
else if @s_jjyt='06'
set @m_jjyt06 = @m_jeif exists(select 1 from inserted) and not exists(select 1 from deleted)--新增
begin
if @s_import_pc='0000'
begin
insert into report_01(source_id,hsdw,kjrq,zjzf_je,sqzf_je,zhzf_je,jjyt01_je,jjyt02_je, jjyt03_je,jjyt04_je,jjyt05_je,jjyt06_je )
values (@n_id,@s_hsdw,@d_kjrq,@m_zjzf,@m_sqzf,@m_zhzf,@m_jjyt01,@m_jjyt02,@m_jjyt03, @m_jjyt04,@m_jjyt05,@m_jjyt06)
end
else
begin
insert into report_01(source_id,hsdw,kjrq,zjzf_je,sqzf_je,zhzf_je,jjyt01_je,jjyt02_je, jjyt03_je,jjyt04_je,jjyt05_je,jjyt06_je )
values (@n_id,@s_hsdw,@d_kjrq,@m_zjzf,@m_sqzf,0,@m_jjyt01,@m_jjyt02,@m_jjyt03,@m_jjyt04, @m_jjyt05,@m_jjyt06)
end
end
if exists(select 1 from inserted) and exists(select 1 from deleted) --修改
begin
update report_01 set zjzf_je=@m_zjzf,sqzf_je=@m_sqzf,zhzf_je=@m_zhzf,jjyt01_je=@m_jjyt01, jjyt02_je=@m_jjyt02,jjyt03_je=@m_jjyt03,jjyt04_je=@m_jjyt04,jjyt05_je=@m_jjyt05,jjyt06_je =@m_jjyt06
where source_id=cast(@n_id as varchar(10))
end if not exists(select 1 from inserted) --删除
begin
--select @n_id = id from deleted --得到ID多余的上面已赋值
delete from report_01 where source_id=cast(@n_id as varchar(10))
end
END
这句的问题。你看看你在SQL下是不是能正常删除这些ID的
SELECT ID FROM DELETED
OPEN CUR
DECLARE @N_ID INT
FETCH NEXT FROM CUR INTO @N_ID
WHILE @@FETCH_STATUS = 0
BEGIN
select @n_id =id from deleted --得到ID
delete from report_01 where source_id=cast(@n_id as varchar(10))
FETCH NEXT FROM CUR INTO @N_ID
END