September 23, 2005 at 2:37 pm
i have a trigger that fires on a table update. the trigger is time tested and true when our GUI updates one recrod at a time (thru VB.NET).
the problem is, that when i run Query analyzer, and update the table, the trigger updates 1 record and stops. is it possible to force it to have the trigger fire for record that was affected?
Thanks for the help!
There are ll types of people: those who understand Roman Numerals, and those who do not.
September 23, 2005 at 2:39 pm
Post the code. You are not using the insert/deleted tables correctly and probabely using variables when you shouldn't.
September 23, 2005 at 3:02 pm
CREATE TRIGGER [UpdateIndividualTables] ON dbo.Individuals
FOR UPDATE
AS
DECLARE @AcctNumber varchar(10)
DECLARE @KamperID int
DECLARE @StaffID varchar(11)
DECLARE @Gender varchar(1)
DECLARE @FirstName varchar(30)
DECLARE @MiddleName varchar(30)
DECLARE @LastName varchar(30)
DECLARE @HAddress varchar(100)
DECLARE @HAddress2 varchar(100)
DECLARE @HCity varchar(40)
DECLARE @HState varchar(2)
DECLARE @HZip varchar(10)
DECLARE @HZip4 varchar(10)
DECLARE @HPhone varchar(15)
DECLARE @HForeign bit
DECLARE @CellPhone varchar(15)
DECLARE @Fax varchar(15)
DECLARE @Email varchar(50)
DECLARE @BadEmailAddress bit
DECLARE @WorkName varchar(100)
DECLARE @WAddress varchar(100)
DECLARE @WAddress2 varchar(100)
DECLARE @WCity varchar(40)
DECLARE @WState varchar(2)
DECLARE @WZip varchar(10)
DECLARE @WZip4 varchar(10)
DECLARE @WPhone varchar(15)
DECLARE @WExtension varchar(5)
DECLARE @WForeign bit
DECLARE @LastUpdate datetime
DECLARE @LastSpouseUpdate datetime
DECLARE @LastUpdatedBy int
DECLARE @Password varchar(20)
DECLARE @Birthdate datetime
DECLARE @Greeting varchar(8)
DECLARE @Suffix varchar(5)
DECLARE @LastFamilyUpdate datetime
DECLARE @LastKamperUpdate datetime
DECLARE @LastStaffUpdate datetime
SELECT @KamperID = KamperID,
@StaffID = StaffID,
@Gender = Gender,
@FirstName = FirstName,
@MiddleName = MiddleName,
@LastName = LastName,
@HAddress = HAddress,
@HAddress2 = HAddress2,
@HCity = HCity,
@HState = HState,
@HZip = HZip,
@HZip4 = HZip4,
@HPhone = HPhone,
@HForeign = HForeign,
@CellPhone = CellPhone,
@Fax = Fax,
@Email = Email,
@BadEmailAddress = BadEmailAddress,
@WorkName = WorkName,
@WAddress = WAddress,
@WAddress2 = WAddress2,
@WCity = WCity,
@WState = WState,
@WZip = WZip,
@WZip4 = WZip4,
@WPhone = WPhone,
@WExtension = WExtension,
@WForeign = WForeign,
@LastUpdate = LastUpdate,
@LastUpdatedBy = LastUpdatedBy,
@Password = [Password],
@Birthdate = Birthdate,
@Greeting = Greeting,
@Suffix = Suffix
FROM Inserted
SELECT @AcctNumber = FamilyUnit.AcctNumber
FROM FamilyUnit
INNER JOIN Inserted
ON FamilyUnit.IndividualID = Inserted.IndividualID
AND FamilyUnit.RelationshipID = 3
--Update PAH in FamilyGroupInfo table******************************************************
IF @AcctNumber IS NOT NULL
BEGIN
--Determine if lastupdate field is the same
SELECT @LastFamilyUpdate = FamilyGroupInfo.LastUpdate
FROM FamilyGroupInfo
WHERE AcctNumber = @AcctNumber
IF @LastFamilyUpdate < @LastUpdate AND @LastFamilyUpdate IS NOT NULL
BEGIN
IF @Gender = 'M'
BEGIN
UPDATE FamilyGroupInfo
SET fFirstName = @FirstName,
fGreeting = @Greeting,
fLastName = @LastName,
LastName = @LastName,
fCellPhone = @CellPhone,
Address = @HAddress,
Address2 = @HAddress2,
City = @HCity,
State = @HState,
ZipCode = @HZip,
ZipCode4 = @HZip4,
HomePhone = @HPhone,
ForeignHome = @HForeign,
BusinessName = @WorkName,
BusAddress = @WAddress,
BusAddress2 = @WAddress2,
BusCity = @WCity,
BusState = @WState,
BusZipCode = @WZip,
BusZipCode4 = @WZip4,
WorkPhone = @WPhone,
WorkExtension = @WExtension,
ForeignBusiness = @WForeign,
Fax = @Fax,
EmailAddress = @Email,
BadEmailAddress = @BadEmailAddress,
[Password] = @Password,
LastUpdate = @LastUpdate,
LastUpdatedBy = @LastUpdatedBy
WHERE AcctNumber = @AcctNumber
END
IF @Gender = 'F'
BEGIN
UPDATE FamilyGroupInfo
SET mFirstName = @FirstName,
mGreeting = @Greeting,
mLastName = @LastName,
LastName = @LastName,
mCellPhone = @CellPhone,
Address = @HAddress,
Address2 = @HAddress2,
City = @HCity,
State = @HState,
ZipCode = @HZip,
ZipCode4 = @HZip4,
HomePhone = @HPhone,
ForeignHome = @HForeign,
BusinessName = @WorkName,
BusAddress = @WAddress,
BusAddress2 = @WAddress2,
BusCity = @WCity,
BusState = @WState,
BusZipCode = @WZip,
BusZipCode4 = @WZip4,
WorkPhone2 = @WPhone,
Work2Extension = @WExtension,
ForeignBusiness = @WForeign,
Fax = @Fax,
EmailAddress = @Email,
BadEmailAddress = @BadEmailAddress,
[Password] = @Password,
LastUpdate = @LastUpdate,
LastUpdatedBy = @LastUpdatedBy
WHERE AcctNumber = @AcctNumber
END
END
END
SELECT @AcctNumber = FamilyUnit.AcctNumber
FROM FamilyUnit
INNER JOIN Inserted
ON FamilyUnit.IndividualID = Inserted.IndividualID
AND FamilyUnit.RelationshipID = 12
--Update Spouse in FamilyGroupInfo table******************************************************
IF @AcctNumber IS NOT NULL
BEGIN
--Determine if lastupdate field is the same
SELECT @LastSpouseUpdate = FamilyGroupInfo.LastSpouseUpdate
FROM FamilyGroupInfo
WHERE AcctNumber = @AcctNumber
IF @LastSpouseUpdate < @LastUpdate AND @LastSpouseUpdate IS NOT NULL
BEGIN
IF @Gender = 'M'
BEGIN
UPDATE FamilyGroupInfo
SET fFirstName = @FirstName,
fGreeting = @Greeting,
fLastName = @LastName,
fCellPhone = @CellPhone,
WorkPhone = @WPhone,
WorkExtension = @WExtension,
LastSpouseUpdate = @LastUpdate,
LastUpdatedBy = @LastUpdatedBy
WHERE AcctNumber = @AcctNumber
END
IF @Gender = 'F'
BEGIN
UPDATE FamilyGroupInfo
SET mFirstName = @FirstName,
mGreeting = @Greeting,
mLastName = @LastName,
mCellPhone = @CellPhone,
WorkPhone2 = @WPhone,
Work2Extension = @WExtension,
LastSpouseUpdate = @LastUpdate,
LastUpdatedBy = @LastUpdatedBy
WHERE AcctNumber = @AcctNumber
END
END
END
--Update Staff Info table******************************************************
IF @StaffID IS NOT NULL
BEGIN
--Determine if lastupdate field is the same
SELECT @LastStaffUpdate = StaffInfo.LastUpdate
FROM StaffInfo
WHERE SSNumber = @StaffID
IF @LastStaffUpdate < @LastUpdate AND @LastStaffUpdate IS NOT NULL
BEGIN
UPDATE StaffInfo
SET FirstName = @FirstName,
MiddleName = @MiddleName,
LastName = @LastName,
Suffix = @Suffix,
Birthdate = @Birthdate,
HAddress = @HAddress,
HCity = @HCity,
HState = @HState,
HZip = @HZip,
HZip4 = @HZip4,
HPhone = @HPhone,
SAddress = @WAddress,
SAddress2 = @WAddress2,
SCity = @WCity,
SState = @WState,
SZip = @WZip,
SZip4 = @WZip4,
SPhone = @WPhone,
Email = @Email,
Sex = @Gender,
LastUpdate = @LastUpdate,
LastUpdateBy = @LastUpdatedBy,
BadEmailAddress = @BadEmailAddress,
CellPhone = @CellPhone,
[Password] = @Password
WHERE SSNumber = @StaffID
END
END
--Update KamperInfo table******************************************************
IF @KamperID IS NOT NULL
BEGIN
--Determine if lastupdate field is the same
SELECT @LastKamperUpdate = KamperInfo.LastUpdate
FROM KamperInfo
WHERE KamperID = @KamperID
IF @LastKamperUpdate < @LastUpdate AND @LastKamperUpdate IS NOT NULL
BEGIN
UPDATE KamperInfo
SET FirstName = @FirstName,
LastName = @LastName,
MI = LEFT(@MiddleName, 1),
Birthdate = @Birthdate,
EmailAddress = @Email,
BadEmailAddress = @BadEmailAddress,
[Password] = @Password,
LastUpdate = @LastUpdate,
LastUpdatedBy = @LastUpdatedBy
WHERE KamperID = @KamperID
END
END
There are ll types of people: those who understand Roman Numerals, and those who do not.
September 24, 2005 at 5:37 am
Try it more set-based, the triggers only does fire once.
Haven't written it all out.
/*INDIVIDUAL INFO*/
UPDATE FamilyGroupInfo
/*FEMALE COLUMNS starting with m??*/
SET
mFirstName = CASE myAccountTAble.GENDER WHEN 'F' THEN myAccountTAble.FirstName ELSE mFirstName END,
mGreeting = CASE myAccountTAble.GENDER WHEN 'F' tHEN myAccountTAble.Greeting ELSE mGreeting END,
mLastName = myAccountTAble.@LastName, /*TODO ADD CASE GENDER*/
LastName = myAccountTAble.@LastName,
mCellPhone = myAccountTAble.@CellPhone,
Address = myAccountTAble.@HAddress,
Address2 = myAccountTAble.HAddress2,
City = myAccountTAble.HCity,
State = myAccountTAble.@HState,
ZipCode = myAccountTAble.HZip,
ZipCode4 = myAccountTAble.HZip4,
HomePhone = myAccountTAble.HPhone,
ForeignHome = myAccountTAble.HForeign,
BusinessName = myAccountTAble.WorkName,
BusAddress = myAccountTAble.@WAddress,
BusAddress2 = myAccountTAble.WAddress2,
BusCity = myAccountTAble.WCity,
BusState = myAccountTAble.WState,
BusZipCode = myAccountTAble.WZip,
BusZipCode4 = myAccountTAble.WZip4,
WorkPhone2 = myAccountTAble.WPhone,
Work2Extension = myAccountTAble.WExtension,
/*MALE COLUMNS starting with f??*/
fFirstName = CASE myAccountTAble.GENDER WHEN 'F' THEN myAccountTAble.FirstName ELSE fFirstName END,
fGreeting = myAccountTAble.Greeting,/*TODO ADD CASE GENDER*/
fLastName = myAccountTAble.LastName,
LastName = myAccountTAble.LastName,
fCellPhone = myAccountTAble.CellPhone,
Address = myAccountTAble.HAddress,
Address2 = myAccountTAble.HAddress2,
City = myAccountTAble.HCity,
State = myAccountTAble.HState,
ZipCode =myAccountTAble.HZip,
ZipCode4 = myAccountTAble.HZip4,
HomePhone = myAccountTAble.HPhone,
ForeignHome = myAccountTAble.HForeign,
BusinessName = myAccountTAble.WorkName,
BusAddress = myAccountTAble.WAddress,
BusAddress2 = myAccountTAble.WAddress2,
BusCity = myAccountTAble.WCity,
BusState = myAccountTAble.WState,
BusZipCode = myAccountTAble.WZip,
BusZipCode4 = myAccountTAble.WZip4,
WorkPhone = myAccountTAble.WPhone,
WorkExtension = myAccountTAble.WExtension,
/*COMMON COLUMNS*/
ForeignBusiness = myAccountTAble.WForeign,
Fax = myAccountTAble.Fax,
EmailAddress = myAccountTAble.Email,
BadEmailAddress = myAccountTAble.BadEmailAddress,
[Password] = myAccountTAble.[Password],
LastUpdate = myAccountTAble.LastUpdate,
LastUpdatedBy = myAccountTAble.LastUpdatedBy
FROM FamilyGroupInfo
INNER JOIN myAccountTAble
(
SELECT Inserted.IndividualID,FamilyUnit.AcctNumber,FamilyUnit.LastUpdate
,Insertedcolumnsforupdates...
FROM FamilyUnit
INNER JOIN Inserted
ON FamilyUnit.IndividualID = Inserted.IndividualID
AND FamilyUnit.RelationshipID = 3 /*?*/
AND FamilyUnit.AcctNumber IS NOT NULL /*NO UPDATING FOR ACCOUNT NULL*/
) As myAccountTAble
ON FamilyGroupInfo.AcctNumber=myAccountTAble.AcctNumber
AND /*this check checks if updating is necessary*/
( FamilyGroupInfo.Lastupdate<myAccountTAble.LastUpdate
AND FamilyGroupInfo.LastUpdate IS NOT NULL
/*Possible to use AND FamilyGroupInfo.lastupdate >0? to avoid NOT)*/
/*NOSMILEY*/)
--Update Spouse in FamilyGroupInfo table******************************************************
/*Relationship 12*/
UPDATE FamilyGroupInfo
.....
INNER JOIN myAccountTAble
(
SELECT Inserted.IndividualID,FamilyUnit.AcctNumber,FamilyUnit.LastUpdate
,Insertedcolumnsforupdates...
FROM FamilyUnit
INNER JOIN Inserted
ON FamilyUnit.IndividualID = Inserted.IndividualID
AND FamilyUnit.RelationshipID = 12 /*?*/
AND FamilyUnit.AcctNumber IS NOT NULL /*NO UPDATING FOR ACCOUNT NULL*/
) As myAccountTAble
ON FamilyGroupInfo.AcctNumber=myAccountTAble.AcctNumber
AND
( FamilyGroupInfo. FamilyGroupInfo.LastSpouseUpdate<myAccountTAble.LastUpdate
AND FamilyGroupInfo. FamilyGroupInfo.LastSpouseUpdate IS NOT NULL
/*Possible to use AND FamilyGroupInfo.lastupdate >0? to avoid NOT)*/
/*NOSMILEY*/)
......
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply