触发器、游标、存储结构

触发器、游标、存储结构

发表于：2006年11月6日 13时2分8秒本文链接：http://user.qzone.qq.com/183481361/blog/34

触发器、游标、存储结构

今天搞了一个上午，就写了这个东西
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Cj_Kcjd]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Cj_Kcjd]
GO

CREATE TABLE [dbo].[Cj_Kcjd] (
[Xnxqh] [varchar] (11) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[kcbm] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Xh] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[KsxzCode] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Xf] [money] null,
[KcCjJD] [money] NULL ,
[Zcj] [money] NULL ,
[XfCjJD] [money] NULL,
CONSTRAINT [PK_Cj_Kcjd] PRIMARY KEY  CLUSTERED
(
[Xnxqh],
[kcbm],
[Xh],
[KsxzCode]
)
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[updateJD]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[updateJD]
GO
CREATE TRIGGER [updateJD] ON [dbo].[cj_kcjd]
FOR UPDATE,INSERT
as
declare @Xnxqh varchar(11)
declare @Kcbm varchar(10)
declare @Xh varchar(20)
declare @KsxzCode varchar(2)
declare @Zcj money
declare @Xf money
declare @KcCjJD money
declare @XfCjJD money
declare @Qsxs money
declare @Jsxs money
declare @Cjjd money

declare c_JD cursor for select xnxqh,kcbm,xh,ksxzcode,zcj,xf from inserted
open c_JD

fetch next from c_JD into @Xnxqh,@Kcbm,@Xh,@Ksxzcode,@Zcj,@Xf
while @@fetch_status = 0
  begin
    select @Zcj=isNull(@Zcj,0)
    select @cjjd=(select Cjjd from cj_cjjddyb where qsxs<=@Zcj and jsxs>=@Zcj)
    select @Cjjd=isNull(@Cjjd,0)  
    select @XfCjJD = @Cjjd / @Xf
    select @KcCjJD = @Zcj * @Xf / @Xf
    update [cj_kcjd] set kccjjd = @KcCjJD,xfcjjd = @XfCjJD where xnxqh = @Xnxqh and Kcbm = @Kcbm and Xh = @Xh and ksxzcode = @Ksxzcode
    fetch next from c_JD into @Xnxqh,@Kcbm,@Xh,@Ksxzcode,@Zcj,@Xf
  end
close c_JD
deallocate c_JD
go


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cj_kcjdupdateNAME]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cj_kcjdupdateNAME]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE .[dbo].[cj_kcjdupdateNAME]

as

set nocount on
declare @Xnxqh varchar(11)
declare @Kcbm varchar(10)
declare @Xh varchar(20)
declare @KsxzCode varchar(2)
declare @Zcj money
declare @Xf money
declare @KcCjJD money
declare @XfCjJD money
declare @Qsxs money
declare @Jsxs money
declare @Cjjd money

declare c_updateJD cursor for select xnxqh,kcbm,xh,ksxzcode,zcj,xf from [cj_kcjd]
open c_updateJD

fetch next from c_updateJD into @Xnxqh,@Kcbm,@Xh,@Ksxzcode,@Zcj,@Xf
while @@fetch_status = 0
  begin
    select @Zcj=isNull(@Zcj,0)
    select @cjjd=(select Cjjd from cj_cjjddyb where qsxs<=@Zcj and jsxs>=@Zcj)
    select @Cjjd=isNull(@Cjjd,0)
    select @XfCjJD = @Cjjd / @Xf
    select @KcCjJD = @Zcj * @Xf / @Xf
    update [cj_kcjd] set kccjjd = @KcCjJD,xfcjjd = @XfCjJD where xnxqh = @Xnxqh and Kcbm = @Kcbm and Xh = @Xh and ksxzcode = @Ksxzcode
    fetch next from c_updateJD into @Xnxqh,@Kcbm,@Xh,@Ksxzcode,@Zcj,@Xf
  end

close c_updateJD
deallocate c_updateJD
go

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
execute cj_kcjdupdateNAME
go