各位大侠,我写了一个触发器,但一执行,发现,触发器的里面的语句全部执行成功了,但我的表没有执行成功,不知道什么原因?如下:SMCustomer,PersonalINfo,CustomizeProduct都Insert成功了,
但ICCard里面确没有Insert成功-- =============================================
-- Create basic Instead Of Trigger
-- =============================================
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'Tr_IC_Ins_SmCus'
AND type = 'TR')
DROP TRIGGER Tr_IC_Ins_SmCus
GOCREATE TRIGGER Tr_IC_Ins_SmCus
ON ICCard
INSTEAD OF INSERT
AS
BEGIN
DECLARE @SmCustomerOID int,@SmPersonalInfoOID int,@CustomizeProductOID int,@ProductOID int --增加短信用户SMCustomer
INSERT INTO SmCustomer(CustomerOID,CustomerID,CustomerName,Telephone,CustomerTypeOID,CardNo,Active)
SELECT Cus.CustomerOID,Cus.CustomerID,Cus.[Name],Cus.Mobile,Cus.CustomerTypeOID,ins.CardNo,1
FROM inserted Ins
Inner Join Account Acc ON Ins.AccountOID=Acc.AccountOID
Inner Join Customer Cus ON Acc.CustomerOID=Cus.CustomerOID --增加个人信息定制三个月
-- EXEC GETOID @SmPersonalInfoOID OUTPUT update SequenceNo set nextNo=nextNo+1
SET @SmPersonalInfoOID=(Select nextNo from SequenceNo) INSERT INTO SmPersonalInfo (SmPersonalInfoOID,CustomerOID,PersonalInfoStatus,OperatorOID,UpdateDateTime,PIStartDate,PIEndDate)
SELECT @SmPersonalInfoOID,SmCus.CustomerOID,1,Null,GetDate(),Convert(DateTime,Convert(Char(10),GETDATE(),120)),DateAdd(MM,3,Convert(DateTime,Convert(Char(10),GETDATE(),120)))
FROM inserted Ins
Inner Join Account Acc ON Ins.AccountOID=Acc.AccountOID
Inner Join Customer Cus ON Acc.CustomerOID=Cus.CustomerOID
Inner Join SmCustomer SmCus ON Cus.CustomerOID=SmCus.CustomerOID
--增加商品(玉米)价格信息定制 三个月
Select @ProductOID=(Select ProductOID From Product Where LTrim(RTrim([Name]))='玉米')
-- EXEC GETOID @CustomizeProductOID OUTPUT
update SequenceNo set nextNo=nextNo+1
SET @CustomizeProductOID=(Select nextNo from SequenceNo) INSERT INTO CustomizeProduct (CustomizeProductOID,CustomerOID,ProductOID,OperatorOID,CPStartDate,LatestDate,UpdateDateTime,CustomizeProductStatus,IsTransFer)
SELECT @CustomizeProductOID,SmCus.CustomerOID,@ProductOID,Null,Convert(DateTime,Convert(Char(10),GETDATE(),120)),DateAdd(MM,3,Convert(DateTime,Convert(Char(10),GETDATE(),120))),GetDate(),1,1
FROM inserted Ins
Inner Join Account Acc ON Ins.AccountOID=Acc.AccountOID
Inner Join Customer Cus ON Acc.CustomerOID=Cus.CustomerOID
Inner Join SmCustomer SmCus ON Cus.CustomerOID=SmCus.CustomerOID
END
GO
但ICCard里面确没有Insert成功-- =============================================
-- Create basic Instead Of Trigger
-- =============================================
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'Tr_IC_Ins_SmCus'
AND type = 'TR')
DROP TRIGGER Tr_IC_Ins_SmCus
GOCREATE TRIGGER Tr_IC_Ins_SmCus
ON ICCard
INSTEAD OF INSERT
AS
BEGIN
DECLARE @SmCustomerOID int,@SmPersonalInfoOID int,@CustomizeProductOID int,@ProductOID int --增加短信用户SMCustomer
INSERT INTO SmCustomer(CustomerOID,CustomerID,CustomerName,Telephone,CustomerTypeOID,CardNo,Active)
SELECT Cus.CustomerOID,Cus.CustomerID,Cus.[Name],Cus.Mobile,Cus.CustomerTypeOID,ins.CardNo,1
FROM inserted Ins
Inner Join Account Acc ON Ins.AccountOID=Acc.AccountOID
Inner Join Customer Cus ON Acc.CustomerOID=Cus.CustomerOID --增加个人信息定制三个月
-- EXEC GETOID @SmPersonalInfoOID OUTPUT update SequenceNo set nextNo=nextNo+1
SET @SmPersonalInfoOID=(Select nextNo from SequenceNo) INSERT INTO SmPersonalInfo (SmPersonalInfoOID,CustomerOID,PersonalInfoStatus,OperatorOID,UpdateDateTime,PIStartDate,PIEndDate)
SELECT @SmPersonalInfoOID,SmCus.CustomerOID,1,Null,GetDate(),Convert(DateTime,Convert(Char(10),GETDATE(),120)),DateAdd(MM,3,Convert(DateTime,Convert(Char(10),GETDATE(),120)))
FROM inserted Ins
Inner Join Account Acc ON Ins.AccountOID=Acc.AccountOID
Inner Join Customer Cus ON Acc.CustomerOID=Cus.CustomerOID
Inner Join SmCustomer SmCus ON Cus.CustomerOID=SmCus.CustomerOID
--增加商品(玉米)价格信息定制 三个月
Select @ProductOID=(Select ProductOID From Product Where LTrim(RTrim([Name]))='玉米')
-- EXEC GETOID @CustomizeProductOID OUTPUT
update SequenceNo set nextNo=nextNo+1
SET @CustomizeProductOID=(Select nextNo from SequenceNo) INSERT INTO CustomizeProduct (CustomizeProductOID,CustomerOID,ProductOID,OperatorOID,CPStartDate,LatestDate,UpdateDateTime,CustomizeProductStatus,IsTransFer)
SELECT @CustomizeProductOID,SmCus.CustomerOID,@ProductOID,Null,Convert(DateTime,Convert(Char(10),GETDATE(),120)),DateAdd(MM,3,Convert(DateTime,Convert(Char(10),GETDATE(),120))),GetDate(),1,1
FROM inserted Ins
Inner Join Account Acc ON Ins.AccountOID=Acc.AccountOID
Inner Join Customer Cus ON Acc.CustomerOID=Cus.CustomerOID
Inner Join SmCustomer SmCus ON Cus.CustomerOID=SmCus.CustomerOID
END
GO
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货