如何用SQL语句导出触发器的脚本

[tcbo]

如何用SQL语句导出触发器的脚本

[tcbo]

zjcxc(邹建)老大，你写这个我用过的，但是有错误啊
有的可以生成，有的不能生成

[zjcxc]

有具体的错误表现么?

[tcbo]

执行print dbo.fgetscript('SINOUR-13','sa','123','HEP_Sample','SALESD_REF_ADD')后出现：

错误号: 0x8004271A
错误源: ODSOLE Extended Procedure
错误描述: srv_convert 中的O

其中'SALESD_REF_ADD'是表SALESD的一个触发器名称

[tcbo]

触发器内容如下：

CREATE TRIGGER SALESD_REF_ADD ON dbo.SALESD INSTEAD OF INSERT AS
DECLARE @FLOWS INT
SELECT @FLOWS=FLOWS FROM ACCSET
DECLARE @nBILLID INT,@nITEMNO INT,@nGOODSID INT,@nGUNITID INT,@nGQTY FLOAT,@nGPRICE FLOAT,@nTAXRATE FLOAT,@nTPRICE FLOAT,@nTAX FLOAT,@nGAMOUNT FLOAT,@nAMOUNT FLOAT,@nBQTY FLOAT,@nBPRICE FLOAT,@nBTPRICE FLOAT,@nCKQTY FLOAT,@nKPQTY FLOAT,@nXZ INT,@nCANCEL INT,@nPARENTID INT,@nSPLIT INT,@nMEMO VARCHAR(64),@nDISCOUNTRATE FLOAT,@nDISCOUNT FLOAT,@nBOMQTY FLOAT,@nUD1 VARCHAR(64),@nUD2 VARCHAR(64),@nUD3 VARCHAR(64),@nUD4 VARCHAR(64),@nUD5 VARCHAR(64),@nREFBILLID INT,@nREFDATATYPE INT,@nREFBILLCODE VARCHAR(30),@nREFITEMNO INT,@nURATE FLOAT,@nSTATUS INT,@nREFCLIENTID INT,@nREFDEPTID INT,@nREFSTOREID INT,@nCVID INT
DECLARE NEWED CURSOR LOCAL FORWARD_ONLY STATIC OPTIMISTIC FOR SELECT BILLID,ITEMNO,GOODSID,GUNITID,GQTY,GPRICE,TAXRATE,TPRICE,TAX,GAMOUNT,AMOUNT,BQTY,BPRICE,BTPRICE,CKQTY,KPQTY,XZ,CANCEL,PARENTID,SPLIT,MEMO,DISCOUNTRATE,DISCOUNT,BOMQTY,UD1,UD2,UD3,UD4,UD5,REFBILLID,REFDATATYPE,REFBILLCODE,REFITEMNO,URATE,STATUS,REFCLIENTID,REFDEPTID,REFSTOREID,CVID FROM INSERTED
OPEN NEWED
FETCH NEXT FROM NEWED INTO @nBILLID,@nITEMNO,@nGOODSID,@nGUNITID,@nGQTY,@nGPRICE,@nTAXRATE,@nTPRICE,@nTAX,@nGAMOUNT,@nAMOUNT,@nBQTY,@nBPRICE,@nBTPRICE,@nCKQTY,@nKPQTY,@nXZ,@nCANCEL,@nPARENTID,@nSPLIT,@nMEMO,@nDISCOUNTRATE,@nDISCOUNT,@nBOMQTY,@nUD1,@nUD2,@nUD3,@nUD4,@nUD5,@nREFBILLID,@nREFDATATYPE,@nREFBILLCODE,@nREFITEMNO,@nURATE,@nSTATUS,@nREFCLIENTID,@nREFDEPTID,@nREFSTOREID,@nCVID
WHILE (@@FETCH_STATUS = 0)
BEGIN
BEGIN TRANSACTION
IF (@nREFBILLID>0 AND @nREFDATATYPE>0 AND @nPARENTID=0)
IF (@FLOWS=1)
BEGIN
IF NOT EXISTS(SELECT BILLID FROM SALE WHERE BILLID=@nREFBILLID AND BILLCODE=@nREFBILLCODE AND CLIENTID=@nREFCLIENTID AND DEPTID=@nREFDEPTID AND STATUS=1 AND FINISHED=0)
BEGIN
RAISERROR('REF_BILL_MODIFY',16,1)
ROLLBACK
RETURN
END

IF NOT EXISTS(SELECT GOODSID FROM SALEDQ WHERE GOODSID=@nGOODSID AND ITEMNO=@nREFITEMNO AND BILLID=@nREFBILLID)
BEGIN
RAISERROR('REF_GOODS_MODIFY',16,1)
ROLLBACK
RETURN
END

IF NOT EXISTS(SELECT GOODSID FROM SALED WHERE GOODSID=@nGOODSID AND ITEMNO=@nREFITEMNO AND BILLID=@nREFBILLID)
BEGIN
RAISERROR('REF_GOODS_MODIFY',16,1)
ROLLBACK
RETURN
END

UPDATE SALED SET EXEQTY=EXEQTY+@nBQTY WHERE GOODSID=@nGOODSID AND ITEMNO=@nREFITEMNO AND BILLID=@nREFBILLID
UPDATE SALED SET EXEQTY=EXEQTY+(@nBQTY*BOMQTY) WHERE PARENTID=@nREFITEMNO AND BILLID=@nREFBILLID
UPDATE SALEDQ SET EXEQTY=EXEQTY+@nBQTY WHERE GOODSID=@nGOODSID AND ITEMNO=@nREFITEMNO AND BILLID=@nREFBILLID
IF NOT EXISTS(SELECT BILLID FROM SALEDQ WHERE EXEQTY<BQTY AND BILLID=@nREFBILLID) UPDATE SALE SET FINISHED=1 WHERE BILLID=@nREFBILLID AND DEPTID=@nREFDEPTID AND CLIENTID=@nREFCLIENTID
END

IF (@FLOWS=2 OR @FLOWS=3)
BEGIN
IF NOT EXISTS(SELECT BILLID FROM SALEO WHERE BILLID=@nREFBILLID AND BILLCODE=@nREFBILLCODE AND STOREID=@nREFSTOREID AND DEPTID=@nREFDEPTID AND STATUS=1)
BEGIN
RAISERROR('REF_BILL_MODIFY',16,1)
ROLLBACK
RETURN
END

IF NOT EXISTS(SELECT GOODSID FROM SALEOD WHERE GOODSID=@nGOODSID AND ITEMNO=@nREFITEMNO AND BILLID=@nREFBILLID)
BEGIN
RAISERROR('REF_GOODS_MODIFY',16,1)
ROLLBACK
RETURN
END

IF NOT EXISTS(SELECT GOODSID FROM SALEOD WHERE GOODSID=@nGOODSID AND ITEMNO=@nREFITEMNO AND BQTY>=SQTY+@nBQTY AND BILLID=@nREFBILLID)

BEGIN
RAISERROR('REF_GOODS_QTY2',16,1)
ROLLBACK
RETURN
END

UPDATE SALEOD SET SQTY=SQTY+@nBQTY WHERE GOODSID=@nGOODSID AND ITEMNO=@nREFITEMNO AND BILLID=@nREFBILLID
IF NOT EXISTS(SELECT BILLID FROM SALEOD WHERE SQTY<BQTY AND BILLID=@nREFBILLID) UPDATE SALEO SET FINISHED=1 WHERE BILLID=@nREFBILLID AND DEPTID=@nREFDEPTID AND STOREID=@nREFSTOREID
END

INSERT INTO SALESD (BILLID,ITEMNO,GOODSID,GUNITID,GQTY,GPRICE,TAXRATE,TPRICE,TAX,GAMOUNT,AMOUNT,BQTY,BPRICE,BTPRICE,CKQTY,KPQTY,XZ,CANCEL,PARENTID,SPLIT,MEMO,DISCOUNTRATE,DISCOUNT,BOMQTY,UD1,UD2,UD3,UD4,UD5,REFBILLID,REFDATATYPE,REFBILLCODE,REFITEMNO,URATE,STATUS,REFCLIENTID,REFDEPTID,REFSTOREID,CVID) SELECT @nBILLID,@nITEMNO,@nGOODSID,@nGUNITID,@nGQTY,@nGPRICE,@nTAXRATE,@nTPRICE,@nTAX,@nGAMOUNT,@nAMOUNT,@nBQTY,@nBPRICE,@nBTPRICE,@nCKQTY,@nKPQTY,@nXZ,@nCANCEL,@nPARENTID,@nSPLIT,@nMEMO,@nDISCOUNTRATE,@nDISCOUNT,@nBOMQTY,@nUD1,@nUD2,@nUD3,@nUD4,@nUD5,@nREFBILLID,@nREFDATATYPE,@nREFBILLCODE,@nREFITEMNO,@nURATE,@nSTATUS,@nREFCLIENTID,@nREFDEPTID,@nREFSTOREID,@nCVID
FETCH NEXT FROM NEWED INTO @nBILLID,@nITEMNO,@nGOODSID,@nGUNITID,@nGQTY,@nGPRICE,@nTAXRATE,@nTPRICE,@nTAX,@nGAMOUNT,@nAMOUNT,@nBQTY,@nBPRICE,@nBTPRICE,@nCKQTY,@nKPQTY,@nXZ,@nCANCEL,@nPARENTID,@nSPLIT,@nMEMO,@nDISCOUNTRATE,@nDISCOUNT,@nBOMQTY,@nUD1,@nUD2,@nUD3,@nUD4,@nUD5,@nREFBILLID,@nREFDATATYPE,@nREFBILLCODE,@nREFITEMNO,@nURATE,@nSTATUS,@nREFCLIENTID,@nREFDEPTID,@nREFSTOREID,@nCVID

IF @@TRANCOUNT > 0
COMMIT TRAN
END
CLOSE NEWED
DEALLOCATE NEWED

[拓狼]

exec sp_helptext '触发器名称'
或者
select object_name(id),text from syscomments where id=object_id('触发器名称')

[chinasaber]

有点难度.

[点点星灯]

在查询分析器中,选择对应的表,选择触发器,生成脚本

[caixia615]

up

[zjcxc]

if exists(select 1 from sysobjects where id=object_id('fgetscript') and objectproperty(id,'IsInlineFunction')=0)
drop function fgetscript
go

/*--
调用sqldmo生成脚本的函数

--邹建 2003.07(引用请保留此信息)--*/

/*--调用实例
print dbo.fgetscript('zj','','','xzkh_sa','syscolumns')

/*--得到所有对象的脚本
declare @name varchar(250)
declare #aa cursor for
select name from sysobjects where xtype not in('S','PK','D','X','L')
open #aa
fetch next from #aa into @name
while @@fetch_status=0
begin
print dbo.fgetscript('zj','','','xzkh_sa',@name)
fetch next from #aa into @name
end
close #aa
deallocate #aa
--*/
--*/

create function fgetscript(
@servername varchar(50) --服务器名
,@userid varchar(50)='sa' --用户名,如果为nt验证方式,则为空
,@password varchar(50)='' --密码
,@databasename varchar(50) --数据库名称
,@objectname varchar(250) --对象名

) returns varchar(8000)
as
begin
declare @re varchar(8000) --返回脚本
declare @srvid int,@dbsid int --定义服务器、数据库集id
declare @dbid int,@tbid int --数据库、表id
declare @err int,@src varchar(255), @desc varchar(255) --错误处理变量

--创建sqldmo对象
exec @err=sp_oacreate 'sqldmo.sqlserver',@srvid output
if @err<>0 goto lberr

--连接服务器
if isnull(@userid,'')='' --如果是 Nt验证方式
begin
exec @err=sp_oasetproperty @srvid,'loginsecure',1
if @err<>0 goto lberr

exec @err=sp_oamethod @srvid,'connect',null,@servername
end
else
exec @err=sp_oamethod @srvid,'connect',null,@servername,@userid,@password

if @err<>0 goto lberr

--获取数据库集
exec @err=sp_oagetproperty @srvid,'databases',@dbsid output
if @err<>0 goto lberr

--获取要取得脚本的数据库id
exec @err=sp_oamethod @dbsid,'item',@dbid output,@databasename
if @err<>0 goto lberr

--获取要取得脚本的对象id
exec @err=sp_oamethod @dbid,'getobjectbyname',@tbid output,@objectname
if @err<>0 goto lberr

--取得脚本
exec @err=sp_oamethod @tbid,'script',@re output
if @err<>0 goto lberr

--print @re
return(@re)

lberr:
exec sp_oageterrorinfo NULL, @src out, @desc out
declare @errb varbinary(4)
set @errb=cast(@err as varbinary(4))
exec master..xp_varbintohexstr @errb,@re out
set @re='错误号: '+@re
+char(13)+'错误源: '+@src
+char(13)+'错误描述: '+@desc
return(@re)
end
go