我在寫程序的時候,要保存數據异動的SQL語句下來,我就在Dingdan 表里面寫了一個觸發器,把SQL程句放到SQLTEXT表里面去:If object_id('DingDan_Insert') is Not null drop trigger DingDan_Insert
goCREATE TRIGGER DingDan_Insert ON DingDan
FOR INSERT
AS
set nocount on
declare @valuea1 varchar(4000)
declare @valuea2 varchar(4000)
declare @valuea3 varchar(4000)
declare @valuea4 smalldatetime //dingdan 定義的是smalldatetime類型
declare @valuea5 int
declare @valuea6 varchar(4000)
declare @valuea7 varchar(4000)
declare @valuea8 bit
declare @valuea9 varchar(4000)
declare @valuea10 varchar(4000)
declare @valuea11 varchar(4000)
declare @valuea12 varchar(4000)
declare @valuea13 bitdeclare cur cursor for
select D_num, productionNo, OrderID, D_date, D_statu, D_userid, address, D_view_statu, meno,user_meno,person,manger,IsEnd from inserted open curfetch next from cur into @valuea1,@valuea2,@valuea3,@valuea4,@valuea5,@valuea6,@valuea7,@valuea8,@valuea9,@valuea10,@valuea11,@valuea12,@valuea13while @@FETCH_STATUS = 0
begin
insert into SqlText (SqlText) values
(' if not exists (select D_num from Dingdan where D_num= ''' + valuea1+''' ) INSERT INTO Dingdan (D_num, productionNo, OrderID, D_date, D_statu, D_userid, address, D_view_statu, meno,user_meno,person,manger,IsEnd) VALUES (''' + @valuea1+ ''',''' + @valuea2+ ''',''' +@valuea3+ ''',''' +@valuea4+ ''' /*我是加上了單引號*/
,' +@valuea5+ ',''' +@valuea6+ ''',''' +@valuea7+ ''',' +@valuea8+ ',''' + @valuea9 +''',''' +@valuea10+ ''',''' +@valuea11+ ''',''' + @valuea12 +''','+@valuea13+')') fetch next from cur into @valuea1,@valuea2,@valuea3,@valuea4,@valuea5,@valuea6,@valuea7,@valuea8,@valuea9,@valuea10,@valuea11,@valuea12,@valuea13 endclose cur
deallocate cur
set nocount off
後來在dingdan 有插入的時候就發生
" 將字元char 轉變成 smalldatetime發生錯誤 "的异常在線等候,請大俠指教
goCREATE TRIGGER DingDan_Insert ON DingDan
FOR INSERT
AS
set nocount on
declare @valuea1 varchar(4000)
declare @valuea2 varchar(4000)
declare @valuea3 varchar(4000)
declare @valuea4 smalldatetime //dingdan 定義的是smalldatetime類型
declare @valuea5 int
declare @valuea6 varchar(4000)
declare @valuea7 varchar(4000)
declare @valuea8 bit
declare @valuea9 varchar(4000)
declare @valuea10 varchar(4000)
declare @valuea11 varchar(4000)
declare @valuea12 varchar(4000)
declare @valuea13 bitdeclare cur cursor for
select D_num, productionNo, OrderID, D_date, D_statu, D_userid, address, D_view_statu, meno,user_meno,person,manger,IsEnd from inserted open curfetch next from cur into @valuea1,@valuea2,@valuea3,@valuea4,@valuea5,@valuea6,@valuea7,@valuea8,@valuea9,@valuea10,@valuea11,@valuea12,@valuea13while @@FETCH_STATUS = 0
begin
insert into SqlText (SqlText) values
(' if not exists (select D_num from Dingdan where D_num= ''' + valuea1+''' ) INSERT INTO Dingdan (D_num, productionNo, OrderID, D_date, D_statu, D_userid, address, D_view_statu, meno,user_meno,person,manger,IsEnd) VALUES (''' + @valuea1+ ''',''' + @valuea2+ ''',''' +@valuea3+ ''',''' +@valuea4+ ''' /*我是加上了單引號*/
,' +@valuea5+ ',''' +@valuea6+ ''',''' +@valuea7+ ''',' +@valuea8+ ',''' + @valuea9 +''',''' +@valuea10+ ''',''' +@valuea11+ ''',''' + @valuea12 +''','+@valuea13+')') fetch next from cur into @valuea1,@valuea2,@valuea3,@valuea4,@valuea5,@valuea6,@valuea7,@valuea8,@valuea9,@valuea10,@valuea11,@valuea12,@valuea13 endclose cur
deallocate cur
set nocount off
後來在dingdan 有插入的時候就發生
" 將字元char 轉變成 smalldatetime發生錯誤 "的异常在線等候,請大俠指教
1.你最好用CONVERT 把 char 轉變成 smalldatetime;
2 insert into SqlText (SqlText) values
(' if not exists (select D_num from Dingdan where D_num= ''' + valuea1+''' )
有误,不知道是不是没有写全;