January 12, 2010 at 5:33 am
I recently upgraded my server from sql server 2000 to sql server 2005. I have a trigger on a table that is supposted to check data and do something to the inserted row. Now the trigger is modifying each row in the table. It is modifying only the bit field changing it from False to True. Im not sure what is causing this behavior.
1. Could it be a setting in the table?
2. Could it be my trigger is written wrong?
ANY input at all? THANKS.
USE [SQL2005_366626_aamlite]
GO
/****** Object: Trigger [dbo].[myWSPANTrigger] Script Date: 01/12/2010 07:30:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[myWSPANTrigger] ON [dbo].[tblWsPan]
INSTEAD OF INSERT
AS
DECLARE
@p_REC nvarchar(10),
@p_MID nvarchar(10),
@p_FNAME nvarchar(50),
@p_LNAME nvarchar(50),
@p_TKTPRICE money,
@p_SEG nvarchar(5),
@p_CARR nvarchar(20),
@p_FLT nvarchar(5),
@p_DDT datetime,
@p_DCPR nvarchar(10),
@p_DCITY nvarchar(50),
@p_ACPR nvarchar(10),
@p_ACITY nvarchar(50),
@p_ADT datetime,
@p_CREATED datetime,
@p_CNDT datetime,
@p_ISOLD bit,
@Existflag int,
@flag bit,
@myNow datetime
--get all the incoming fields
DECLARE @REC nvarchar(10)
SET @REC= (SELECT REC FROM Inserted)
DECLARE @MID nvarchar(10)
SET @MID= (SELECT MeetingID FROM Inserted)
DECLARE @FNAME nvarchar(50)
SET @FNAME= (SELECT FNAME FROM Inserted)
DECLARE @LNAME nvarchar(50)
SET @LNAME= (SELECT LNAME FROM Inserted)
DECLARE @TKTPRICE money
SET @TKTPRICE= (SELECT TKTPRICE FROM Inserted)
DECLARE @SEG nvarchar(5)
SET @SEG= (SELECT SEG FROM Inserted)
DECLARE @CARR nvarchar(20)
SET @CARR= (SELECT CARR FROM Inserted)
DECLARE @FLT nvarchar(5)
SET @FLT= (SELECT FLT FROM Inserted)
DECLARE @DDT datetime
SET @DDT= (SELECT DDT FROM Inserted)
DECLARE @DCPR nvarchar(10)
SET @DCPR= (SELECT DCPR FROM Inserted)
DECLARE @DCITY nvarchar(50)
SET @DCITY= (SELECT DCITY FROM Inserted)
DECLARE @ACPR nvarchar(10)
SET @ACPR= (SELECT ACPR FROM Inserted)
DECLARE @ACITY nvarchar(50)
SET @ACITY= (SELECT ACITY FROM Inserted)
DECLARE @ADT datetime
SET @ADT= (SELECT ADT FROM Inserted)
DECLARE @CREATED datetime
SET @CREATED= (SELECT CREATED FROM Inserted)
DECLARE @CNDT datetime
SET @CNDT= (SELECT CNDT FROM Inserted)
DECLARE @ISOLD bit
SET @ISOLD= (SELECT ISOLD FROM Inserted)
SET @flag=1
BEGIN
Set @Existflag=(Select Count(*) From [tblWsPan] Where REC=@REC AND SEG=@SEG AND FNAME=@FNAME AND LNAME=@LNAME)
--Record not existing
If @Existflag=0
Begin
Insert Into [tblWsPan] (REC, MeetingID, FNAME,LNAME,TKTPRICE,SEG,CARR,FLT,DDT,DCPR,DCITY,ACPR,ACITY,ADT,CREATED,CNDT,ISOLD)
Values (@REC, @MID, @FNAME, @LNAME, @TKTPRICE, @SEG, @CARR, @FLT, @DDT, @DCPR,@DCITY, @ACPR,@ACITY, @ADT, @CREATED, @CNDT, @ISOLD)
End
Else
--record existing, fetch the match record
Begin
Select @p_REC =T.REC,
@p_MID =T.MeetingID,
@p_FNAME =T.FNAME,
@p_LNAME =T.LNAME,
@p_TKTPRICE= T.TKTPRICE,
@p_SEG = T.SEG,
@p_CARR = T.CARR,
@p_FLT = T.FLT,
@p_DDT =T.DDT,
@p_DCPR=T.DCPR,
@p_DCITY= T.DCITY,
@p_ACPR=T.ACPR,
@p_ACITY =T.ACITY,
@p_ADT= T.ADT,
@p_CREATED= T.CREATED,
@p_CNDT =T.CNDT
FROM [tblWsPan] T
WHERE (T.REC=@REC AND T.ISOLD=0 AND T.SEG=@SEG AND T.FNAME=@FNAME AND T.LNAME=@LNAME)
--compare every record, if any one of them doesn't match, flag=0
IF NOT @p_MID=@MID
BEGIN
SET @flag=0
End
IF NOT @p_FNAME=@FNAME
BEGIN
SET @flag=0
End
IF NOT @p_LNAME=@LNAME
BEGIN
SET @flag=0
End
IF NOT @p_TKTPRICE=@TKTPRICE
BEGIN
SET @flag=0
End
IF NOT @p_SEG=@SEG
BEGIN
SET @flag=0
End
IF NOT @p_CARR=@CARR
BEGIN
SET @flag=0
SET @myNow = GetDate()
Begin
Insert Into [tblWsPanChanges] (REC,MeetingID,FNAME,LNAME,SEG,FIELDNAME,OLDVALUE,NEWVALUE,CHANGEDATETIME)
Values (@REC, @MID, @FNAME, @LNAME,@SEG,'CARRIER',@p_CARR,@CARR,@myNow)
End
End
IF NOT @p_FLT=@FLT
BEGIN
SET @flag=0
SET @myNow = GetDate()
Begin
Insert Into [tblWsPanChanges] (REC,MeetingID,FNAME,LNAME,SEG,FIELDNAME,OLDVALUE,NEWVALUE,CHANGEDATETIME)
Values (@REC, @MID, @FNAME, @LNAME,@SEG,'FLIGHT',@p_FLT,@FLT,@myNow)
End
End
IF NOT @p_DDT=@DDT
BEGIN
SET @flag=0
SET @myNow = GetDate()
Begin
Insert Into [tblWsPanChanges] (REC,MeetingID,FNAME,LNAME,SEG,FIELDNAME,OLDVALUE,NEWVALUE,CHANGEDATETIME)
Values (@REC, @MID, @FNAME, @LNAME,@SEG,'DEPT DATE TIME',@p_DDT,@DDT,@myNow)
End
End
IF NOT @p_DCPR=@DCPR
BEGIN
SET @flag=0
SET @myNow = GetDate()
Begin
Insert Into [tblWsPanChanges] (REC,MeetingID,FNAME,LNAME,SEG,FIELDNAME,OLDVALUE,NEWVALUE,CHANGEDATETIME)
Values (@REC, @MID, @FNAME, @LNAME,@SEG, 'DEPT CITY PAIR',@p_DCPR,@DCPR,@myNow)
End
End
IF NOT @p_DCITY=@DCITY
BEGIN
SET @flag=0
SET @myNow = GetDate()
Begin
Insert Into [tblWsPanChanges] (REC,MeetingID,FNAME,LNAME,SEG,FIELDNAME,OLDVALUE,NEWVALUE,CHANGEDATETIME)
Values (@REC, @MID, @FNAME, @LNAME,@SEG,'DEPT CITY',@p_DCITY,@DCITY,@myNow)
End
End
IF NOT @p_ACPR=@ACPR
BEGIN
SET @flag=0
SET @myNow = GetDate()
Begin
Insert Into [tblWsPanChanges] (REC,MeetingID,FNAME,LNAME,SEG,FIELDNAME,OLDVALUE,NEWVALUE,CHANGEDATETIME)
Values (@REC, @MID, @FNAME, @LNAME,@SEG,'ARR CITY PAIR',@p_ACPR,@ACPR,@myNow)
End
End
IF NOT @p_ACITY=@ACITY
BEGIN
SET @flag=0
SET @myNow = GetDate()
Begin
Insert Into [tblWsPanChanges] (REC,MeetingID,FNAME,LNAME,SEG,FIELDNAME,OLDVALUE,NEWVALUE,CHANGEDATETIME)
Values (@REC, @MID, @FNAME, @LNAME,@SEG, 'ARR CITY',@p_ACITY,@ACITY,@myNow)
End
End
IF NOT @p_ADT=@ADT
BEGIN
SET @flag=0
SET @myNow = GetDate()
Begin
Insert Into [tblWsPanChanges] (REC,MeetingID,FNAME,LNAME,SEG,FIELDNAME,OLDVALUE,NEWVALUE,CHANGEDATETIME)
Values (@REC, @MID, @FNAME, @LNAME,@SEG, 'ARR DATE TIME',@p_ADT,@ADT,@myNow)
End
End
IF NOT @p_CREATED=@CREATED
BEGIN
SET @flag=0
End
--capture any change
IF @flag=0
Begin
BEGIN
--Update Cancel date and set is old field to be true
UPDATE [tblWsPan] SET CNDT=GetDate(),ISOLD=0 Where REC=@REC AND SEG=@SEG AND FNAME=@FNAME AND LNAME=@LNAME
End
Begin
--insert new value
Insert Into [tblWsPan] (REC, MeetingID, FNAME,LNAME,TKTPRICE,SEG,CARR,FLT,DDT,DCPR,DCITY,ACPR,ACITY,ADT,CREATED,CNDT,ISOLD)
Values (@REC, @MID, @FNAME, @LNAME, @TKTPRICE, @SEG, @CARR, @FLT, @DDT,@DCPR, @DCITY,@ACPR, @ACITY, @ADT, @CREATED, @CNDT, @ISOLD)
End
end
END
END
January 12, 2010 at 5:39 am
First things first, that trigger will not work correctly when more than one row is inserted into the table. You're selecting values from the inserted table into variables, what's going to happen there if 10 rows are inserted and those 10 rows are all in the inserted table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2010 at 8:38 am
NULL handling is a bit suspect too. I have made a few changes, while keeping the structure similar. Let me know if it improves things at all. There is considerable scope to improve/replace this trigger as I am sure you are aware, but I have endeavoured to leave it at least recognisable for the time being.
USE [SQL2005_366626_aamlite]
GO
/****** Object: Trigger [dbo].[myWSPANTrigger] Script Date: 01/12/2010 07:30:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[myWSPANTrigger] ON [dbo].[tblWsPan]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
-- Check for no rows
IF NOT EXISTS (SELECT * FROM inserted) RETURN;
-- Check for multi-row INSERT
IF (SELECT COUNT(*) FROM (SELECT TOP (2) * FROM inserted)) > 1
BEGIN
RAISERROR('This trigger does not handle multi-row inserts');
ROLLBACK;
RETURN;
END;
DECLARE @p_REC nvarchar(10),
@p_MID nvarchar(10),
@p_FNAME nvarchar(50),
@p_LNAME nvarchar(50),
@p_TKTPRICE money,
@p_SEG nvarchar(5),
@p_CARR nvarchar(20),
@p_FLT nvarchar(5),
@p_DDT datetime,
@p_DCPR nvarchar(10),
@p_DCITY nvarchar(50),
@p_ACPR nvarchar(10),
@p_ACITY nvarchar(50),
@p_ADT datetime,
@p_CREATED datetime,
@p_CNDT datetime,
@p_ISOLD bit,
@Existflag int,
@flag bit,
@myNow datetime,
@REC nvarchar(10)
@MID nvarchar(10)
@FNAME nvarchar(50)
@LNAME nvarchar(50)
@TKTPRICE money
@SEG nvarchar(5)
@CARR nvarchar(20)
@FLT nvarchar(5)
@DDT datetime
@DCPR nvarchar(10)
@DCITY nvarchar(50)
@ACPR nvarchar(10)
@ACITY nvarchar(50)
@ADT datetime
@CREATED datetime
@CNDT datetime
@ISOLD bit;
SELECT @REC = REC,
@MID = MeetingID,
@FNAME = FNAME,
@LNAME = LNAME,
@TKTPRICE = TKTPRICE,
@SEG = SEG,
@CARR = CARR,
@FLT = FLT,
@DDT = DDT,
@DCPR = DCPR,
@DCITY = DCITY,
@ACPR = ACPR,
@ACITY = ACITY,
@ADT = ADT,
@CREATED = CREATED,
@CNDT = CNDT,
@ISOLD = ISOLD
FROM inserted;
IF NOT EXISTS
(
SELECT *
FROM tblWsPan
WHERE (REC = @REC OR (REC IS NULL AND @REC IS NULL))
AND (SEG = @SEG OR (SEG IS NULL AND @SEG IS NULL))
AND (FNAME = @FNAME OR (FNAME IS NULL AND @FNAME IS NULL))
AND (LNAME = @LNAME OR (LNAME IS NULL AND @LNAME IS NULL))
)
BEGIN
INSERT [tblWsPan]
(REC, MeetingID, FNAME,LNAME,TKTPRICE,SEG,CARR,FLT,DDT,DCPR,DCITY,ACPR,ACITY,ADT,CREATED,CNDT,ISOLD)
VALUES (@REC, @MID, @FNAME, @LNAME, @TKTPRICE, @SEG, @CARR, @FLT, @DDT, @DCPR,@DCITY, @ACPR,@ACITY, @ADT, @CREATED, @CNDT, @ISOLD);
END
ELSE
BEGIN
SELECT @p_REC =T.REC,
@p_MID =T.MeetingID,
@p_FNAME =T.FNAME,
@p_LNAME =T.LNAME,
@p_TKTPRICE= T.TKTPRICE,
@p_SEG = T.SEG,
@p_CARR = T.CARR,
@p_FLT = T.FLT,
@p_DDT =T.DDT,
@p_DCPR=T.DCPR,
@p_DCITY= T.DCITY,
@p_ACPR=T.ACPR,
@p_ACITY =T.ACITY,
@p_ADT= T.ADT,
@p_CREATED= T.CREATED,
@p_CNDT =T.CNDT
FROM [tblWsPan] T
WHERE T.REC=@REC
AND T.ISOLD=0
AND T.SEG=@SEG
AND T.FNAME=@FNAME
AND T.LNAME=@LNAME;
SET @flag = 1;
IF ((@p_MID <> @MID) OR (@p_MID IS NULL AND @MID IS NOT NULL) OR (@MID IS NULL AND @p_MID IS NOT NULL))
OR ((@p_FNAME <> @FNAME) OR (@p_FNAME IS NULL AND @FNAME IS NOT NULL) OR (@FNAME IS NULL AND @p_FNAME IS NOT NULL))
OR ((@p_LNAME <> @LNAME) OR (@p_LNAME IS NULL AND @LNAME IS NOT NULL) OR (@LNAME IS NULL AND @p_LNAME IS NOT NULL))
OR ((@p_TKTPRICE <> @TKTPRICE) OR (@p_TKTPRICE IS NULL AND @TKTPRICE IS NOT NULL) OR (@TKTPRICE IS NULL AND @p_TKTPRICE IS NOT NULL))
OR ((@p_SEG <> @SEG) OR (@p_SEG IS NULL AND @SEG IS NOT NULL) OR (@SEG IS NULL AND @p_SEG IS NOT NULL))
OR ((@p_CREATED <> @CREATED) OR (@p_CREATED IS NULL AND @CREATED IS NOT NULL) OR (@CREATED IS NULL AND @p_CREATED IS NOT NULL))
BEGIN
SET @flag = 0;
END;
IF ((@p_CARR <> @CARR) OR (@p_CARR IS NULL AND @CARR IS NOT NULL) OR (@CARR IS NULL AND @p_CARR IS NOT NULL))
BEGIN
SET @flag = 0;
INSERT [tblWsPanChanges]
(REC,MeetingID,FNAME,LNAME,SEG,FIELDNAME,OLDVALUE,NEWVALUE,CHANGEDATETIME)
VALUES (@REC, @MID, @FNAME, @LNAME,@SEG,'CARRIER',@p_CARR,@CARR,GETDATE())
END;
IF ((@p_FLT <> @FLT) OR (@p_FLT IS NULL AND @FLT IS NOT NULL) OR (@FLT IS NULL AND @p_FLT IS NOT NULL))
BEGIN
SET @flag = 0;
INSERT [tblWsPanChanges]
(REC,MeetingID,FNAME,LNAME,SEG,FIELDNAME,OLDVALUE,NEWVALUE,CHANGEDATETIME)
VALUES (@REC, @MID, @FNAME, @LNAME,@SEG,'FLIGHT',@p_FLT,@FLT,GETDATE())
END;
IF ((@p_DDT <> @DDT) OR (@p_DDT IS NULL AND @DDT IS NOT NULL) OR (@DDT IS NULL AND @p_DDT IS NOT NULL))
BEGIN
SET @flag = 0;
INSERT [tblWsPanChanges]
(REC,MeetingID,FNAME,LNAME,SEG,FIELDNAME,OLDVALUE,NEWVALUE,CHANGEDATETIME)
VALUES (@REC, @MID, @FNAME, @LNAME,@SEG,'DEPT DATE TIME',@p_DDT,@DDT,GETDATE())
END;
IF ((@p_DCPR <> @DCPR) OR (@p_DCPR IS NULL AND @DCPR IS NOT NULL) OR (@DCPR IS NULL AND @p_DCPR IS NOT NULL))
BEGIN
SET @flag = 0;
INSERT [tblWsPanChanges]
(REC,MeetingID,FNAME,LNAME,SEG,FIELDNAME,OLDVALUE,NEWVALUE,CHANGEDATETIME)
VALUES (@REC, @MID, @FNAME, @LNAME,@SEG,'DEPT CITY PAIR',@p_DCPR,@DCPR,GETDATE())
END;
IF ((@p_DCITY <> @DCITY) OR (@p_DCITY IS NULL AND @DCITY IS NOT NULL) OR (@DCITY IS NULL AND @p_DCITY IS NOT NULL))
BEGIN
SET @flag = 0;
INSERT [tblWsPanChanges]
(REC,MeetingID,FNAME,LNAME,SEG,FIELDNAME,OLDVALUE,NEWVALUE,CHANGEDATETIME)
VALUES (@REC, @MID, @FNAME, @LNAME,@SEG,'DEPT CITY',@p_DCITY,@DCITY,GETDATE())
END;
IF ((@p_ACPR <> @ACPR) OR (@p_ACPR IS NULL AND @ACPR IS NOT NULL) OR (@ACPR IS NULL AND @p_ACPR IS NOT NULL))
BEGIN
SET @flag = 0;
INSERT [tblWsPanChanges]
(REC,MeetingID,FNAME,LNAME,SEG,FIELDNAME,OLDVALUE,NEWVALUE,CHANGEDATETIME)
VALUES (@REC, @MID, @FNAME, @LNAME,@SEG,'ARR CITY PAIR',@p_ACPR,@ACPR,GETDATE())
END;
IF ((@p_ACITY <> @ACITY) OR (@p_ACITY IS NULL AND @ACITY IS NOT NULL) OR (@ACITY IS NULL AND @p_ACITY IS NOT NULL))
BEGIN
SET @flag = 0;
INSERT [tblWsPanChanges]
(REC,MeetingID,FNAME,LNAME,SEG,FIELDNAME,OLDVALUE,NEWVALUE,CHANGEDATETIME)
VALUES (@REC, @MID, @FNAME, @LNAME,@SEG,'ARR CITY',@p_ACITY,@ACITY,GETDATE())
END;
IF ((@p_ADT <> @ADT) OR (@p_ADT IS NULL AND @ADT IS NOT NULL) OR (@ADT IS NULL AND @p_ADT IS NOT NULL))
BEGIN
SET @flag = 0;
INSERT [tblWsPanChanges]
(REC,MeetingID,FNAME,LNAME,SEG,FIELDNAME,OLDVALUE,NEWVALUE,CHANGEDATETIME)
VALUES (@REC, @MID, @FNAME, @LNAME,@SEG,'ARR DATE TIME',@p_ADT,@ADT,GETDATE())
END;
END;
IF @flag = 0
BEGIN
UPDATE [tblWsPan]
SET CNDT=GetDate(),
ISOLD=0
WHERE (REC=@REC OR (REC IS NULL AND @REC IS NULL))
AND (SEG=@SEG OR (SEG IS NULL AND @SEG IS NULL))
AND (FNAME=@FNAME OR (FNAME IS NULL AND @FNAME IS NULL))
AND (LNAME=@LNAME OR (LNAME IS NULL AND @LNAME IS NULL))
INSERT [tblWsPan]
(REC, MeetingID, FNAME,LNAME,TKTPRICE,SEG,CARR,FLT,DDT,DCPR,DCITY,ACPR,ACITY,ADT,CREATED,CNDT,ISOLD)
VALUES (@REC, @MID, @FNAME, @LNAME, @TKTPRICE, @SEG, @CARR, @FLT, @DDT,@DCPR, @DCITY,@ACPR, @ACITY, @ADT, @CREATED, @CNDT, @ISOLD)
END;
END;
GO
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply