January 18, 2011 at 8:44 am
Hi,
First off, here's the DB version:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
As far as I understand, triggers are invoked when data modification events happen to the table with which the trigger is associated.
The table in question (essentially a user table) has 3 triggers on it, doing similar checks which result in updating a separate table (essentially activity type tracking). The trigger I'm concerned with is "for insert", as it doesn't seem to to be invoked in all the cases it needs to. Here are the cases:
- A php website using the table does inline insert commands (invokes the trigger every time).
- A .NET 2.0 webservice uses a stored procedure to insert into the table (which seems to have only invoked the trigger once).
- A separate MS Access UI at another physical location directly connects to & inserts into the table (which seems to have never invoked the trigger).
Don't data modification events happen when stored procedures & MS Access UI's insert into a table?
Below are the trigger & sproc. Sorry about the formatting! In the sproc, the insert is the last option in the series of if else if blocks.
Can someone get back to me ASAP? I'm pretty new to triggers, & I may have set it up incorrectly...
Thanks in advance!
- O8
Trigger
ALTER trigger [dbo].[InsertJoinWebsiteEVS]
on [dbo].[Members]
for insert
as
SET NOCOUNT ON
-- Website registration with receive monthly opt in ticked (full)
insert into EVSActions ( Membership, EVSActionTypeID, ActionDate )
select
Inserted.Membership,
1,
getdate()
from
Inserted
where
Inserted.bitUser_MailListIP = 1
and Inserted.bitUser_MonthlyOptIn = 1
and Inserted.Deleted = 0
-- Website registration with receive monthly opt in NOT ticked (partial)
insert into EVSActions ( Membership, EVSActionTypeID, ActionDate )
select
Inserted.Membership,
2,
getdate()
from
Inserted
where
Inserted.bitUser_MailListIP = 1
and Inserted.bitUser_MonthlyOptIn = 0
and Inserted.Deleted = 0
Stored Procedure
ALTER PROCEDURE [dbo].[spGrahamMemberInsertUpdate]
@Title nvarchar(16),
@Forename nvarchar(30),
@Surname nvarchar(30),
@Gender tinyint,
@JobTitle nvarchar(50),
@PrivateAddressLine1 nvarchar(60) = NULL,
@PrivateAddressLine2 nvarchar(60) = NULL,
@PrivateAddressLine3 nvarchar(35) = NULL,
@PrivateTownCity nvarchar(35) = NULL,
@PrivateCounty nvarchar(35) = NULL,
@PrivatePostcode nvarchar(8) = NULL,
@PrivateCountry nvarchar(50) = NULL,
@HomeOrWork tinyint,
@WorkAddressLine1 nvarchar(60),
@WorkAddressLine2 nvarchar(60),
@WorkAddressLine3 nvarchar(35),
@WorkTownCity nvarchar(35),
@WorkCounty nvarchar(35),
@WorkPostcode nvarchar(8),
@WorkCountry nvarchar(50),
@PrivatePhone nvarchar(50),
@PrivateMobile nvarchar(50),
@Email nvarchar(60) = NULL,
@EmailMIL nvarchar(60) = NULL,
@WorkPhone nvarchar(50),
@WorkMobile nvarchar(50),
@MemberHear int,
@JobType int,
@InPractice bit,
@Aptamil bit,
@StudyDays bit,
@ProductUpdates bit,
@LearningCurves bit,
@InfantToddler bit,
@UserDataNoTel bit,
@UserDataNoMail bit,
@UserDataNoEmail bit,
@ciboodleId nvarchar(50),
@InPracticeOptions varchar(20),
@UpdateSource nvarchar(50),
@MonthlyOptIn bit,
@SOURCE int,
@MEMBERSHIP int output
AS
SET NOCOUNT ON
/*
There are now different options here
(1) UPDATEBYCB = @ciboodleId is supplied and user exisits
(2) UPDATEBYEMAIL = @ciboodleId is not supplied and user exisits based on emails lookup
(3) NORMALINSERT = @ciboodleId is supplied and user does not exisit
(4) NORMALINSERT = @ciboodleId is not supplied and user does not exisits based on emails lookup
*/
DECLARE @UPDATEBYCB bit;
DECLARE @UPDATEBYIPEMAIL bit;
DECLARE @UPDATEBYAPTAMILEMAIL bit;
DECLARE @NORMALINSERT bit;
SET @UPDATEBYCB = 0;
SET @UPDATEBYIPEMAIL = 0;
SET @UPDATEBYAPTAMILEMAIL = 0;
SET @NORMALINSERT = 1;
IF (@ciboodleId <> '' AND @ciboodleId IS NOT NULL) AND EXISTS(SELECT [Membership] FROM [dbo].[tblPSMH_Members] WHERE [ciboodleId] = @ciboodleId)
BEGIN
SET @UPDATEBYCB = 1;
SET @NORMALINSERT = 0;
END
ELSE IF(@Email <> '' AND @Email IS NOT NULL) AND EXISTS(SELECT [Membership] FROM [dbo].[tblPSMH_Members] WHERE = @Email)
BEGIN
SET @UPDATEBYIPEMAIL = 1;
SET @NORMALINSERT = 0;
END
ELSE IF(@EmailMIL <> '' AND @EmailMIL IS NOT NULL) AND EXISTS(SELECT [Membership] FROM [dbo].[tblPSMH_Members] WHERE [emailMIL] = @EmailMIL)
BEGIN
SET @UPDATEBYAPTAMILEMAIL = 1;
SET @NORMALINSERT = 0;
END
IF(@UPDATEBYCB = 1)
BEGIN
UPDATE [dbo].[tblPSMH_Members] SET
[TITLE] = @Title,
[FORENAME] = @Forename,
[SURNAME] = @Surname,
[SEX] = @Gender,
[JOBTITLE] = @JobTitle,
[JobType] = @JobType,
[AD1] = @PrivateAddressLine1,
[AD2] = @PrivateAddressLine2,
[AD3] = @PrivateAddressLine3,
[TOWN] = @PrivateTownCity,
[COUNTY] = @PrivateCounty,
[POSTCODE] = @PrivatePostcode,
[COUNTRY] = @PrivateCountry,
[HomeOrWork] = @HomeOrWork,
[WORKAD1] = @WorkAddressLine1,
[WORKAD2] = @WorkAddressLine2,
[WORKAD3] = @WorkAddressLine3,
[WORKTOWN] = @WorkTownCity,
[WORKCOUNTY] = @WorkCounty,
[WORKPOSTCODE] = @WorkPostcode,
[WORKCOUNTRY] = @WorkCountry,
= @PrivatePhone,
[MOBILE] = @PrivateMobile,
[WORKTEL] = @WorkPhone,
[WORKMOB] = @WorkMobile,
[bitUser_MailListIP] = CASE @InPractice
WHEN NULL THEN 0
ELSE @InPractice
END,
[bitUser_MailListMilupaCRM] = CASE @Aptamil
WHEN NULL THEN 0
ELSE @Aptamil
END,
[bitStudyMIL] = CASE @Aptamil
WHEN 1 THEN @StudyDays
ELSE 0
END ,
[bitProductMIL] = CASE @Aptamil
WHEN 1 THEN @ProductUpdates
ELSE 0
END ,
[bitUser_MailListIP_options] = CASE @InPractice
WHEN 1 THEN @InPracticeOptions
ELSE NULL
END ,
[bitUser_LearningCurveDB] = @LearningCurves,
[bitUser_InfantToddlerDB] = @InfantToddler,
[bitUser_DataNoTel] = @UserDataNoTel,
[bitUser_DataNoPost] =@UserDataNoMail,
[bitUser_DataNoEmail]=@UserDataNoEmail,
[LastUpdated] = GetDate(),
[id_PSMH_MembersHear] = @MemberHear,
[UpdateSource] = @UpdateSource,
[bitUser_MonthlyOptIn] = @MonthlyOptIn,
= @Email,
[emailMIL] = @EmailMIL,
[SOURCE] = @SOURCE
WHERE
[ciboodleId] = @ciboodleId
SET @MEMBERSHIP = (select MEMBERSHIP from [tblPSMH_Members] where [ciboodleId] = @ciboodleId)
END
ELSE if (@UPDATEBYIPEMAIL = 1)
BEGIN
UPDATE [dbo].[tblPSMH_Members]
SET
[TITLE] = @Title,
[FORENAME] = @Forename,
[SURNAME] = @Surname,
[SEX] = @Gender,
[JOBTITLE] = @JobTitle,
[JobType] = @JobType,
[AD1] = @PrivateAddressLine1,
[AD2] = @PrivateAddressLine2,
[AD3] = @PrivateAddressLine3,
[TOWN] = @PrivateTownCity,
[COUNTY] = @PrivateCounty,
[POSTCODE] = @PrivatePostcode,
[COUNTRY] = @PrivateCountry,
[HomeOrWork] = @HomeOrWork,
[WORKAD1] = @WorkAddressLine1,
[WORKAD2] = @WorkAddressLine2,
[WORKAD3] = @WorkAddressLine3,
[WORKTOWN] = @WorkTownCity,
[WORKCOUNTY] = @WorkCounty,
[WORKPOSTCODE]= @WorkPostcode,
[WORKCOUNTRY] = @WorkCountry,
= @PrivatePhone,
[MOBILE] = @PrivateMobile,
[WORKTEL] = @WorkPhone,
[WORKMOB] = @WorkMobile,
[bitUser_MailListIP] = CASE @InPractice
WHEN NULL THEN 0
ELSE @InPractice
END,
[bitUser_MailListMilupaCRM] = CASE @Aptamil
WHEN NULL THEN 0
ELSE @Aptamil
END,
[bitStudyMIL] = CASE @Aptamil
WHEN 1 THEN @StudyDays
ELSE 0
END ,
[bitProductMIL] = CASE @Aptamil
WHEN 1 THEN @ProductUpdates
ELSE 0
END ,
[bitUser_MailListIP_options] = CASE @InPractice
WHEN 1 THEN @InPracticeOptions
ELSE NULL
END ,
[bitUser_LearningCurveDB] = @LearningCurves,
[bitUser_InfantToddlerDB] = @InfantToddler,
[bitUser_DataNoTel] = @UserDataNoTel,
[bitUser_DataNoPost] =@UserDataNoMail,
[bitUser_DataNoEmail]=@UserDataNoEmail,
[LastUpdated] = GetDate(),
[id_PSMH_MembersHear] = @MemberHear,
[ciboodleId] = @ciboodleId,
[UpdateSource] = @UpdateSource,
[bitUser_MonthlyOptIn] = @MonthlyOptIn,
[SOURCE] = @SOURCE
WHERE = @Email
SET @MEMBERSHIP = (select MEMBERSHIP from [tblPSMH_Members] where = @Email)
END
ELSE if (@UPDATEBYAPTAMILEMAIL = 1)
BEGIN
UPDATE [dbo].[tblPSMH_Members]
SET
[TITLE] = @Title,
[FORENAME] = @Forename,
[SURNAME] = @Surname,
[SEX] = @Gender,
[JOBTITLE] = @JobTitle,
[JobType] = @JobType,
[AD1] = @PrivateAddressLine1,
[AD2] = @PrivateAddressLine2,
[AD3] = @PrivateAddressLine3,
[TOWN] = @PrivateTownCity,
[COUNTY] = @PrivateCounty,
[POSTCODE] = @PrivatePostcode,
[COUNTRY] = @PrivateCountry,
[HomeOrWork] = @HomeOrWork,
[WORKAD1] = @WorkAddressLine1,
[WORKAD2] = @WorkAddressLine2,
[WORKAD3] = @WorkAddressLine3,
[WORKTOWN] = @WorkTownCity,
[WORKCOUNTY] = @WorkCounty,
[WORKPOSTCODE]= @WorkPostcode,
[WORKCOUNTRY] = @WorkCountry,
= @PrivatePhone,
[MOBILE] = @PrivateMobile,
[WORKTEL] = @WorkPhone,
[WORKMOB] = @WorkMobile,
[bitUser_MailListIP] = CASE @InPractice
WHEN NULL THEN 0
ELSE @InPractice
END,
[bitUser_MailListMilupaCRM] = CASE @Aptamil
WHEN NULL THEN 0
ELSE @Aptamil
END,
[bitStudyMIL] = CASE @Aptamil
WHEN 1 THEN @StudyDays
ELSE 0
END ,
[bitProductMIL] = CASE @Aptamil
WHEN 1 THEN @ProductUpdates
ELSE 0
END ,
[bitUser_MailListIP_options] = CASE @InPractice
WHEN 1 THEN @InPracticeOptions
ELSE NULL
END ,
[bitUser_LearningCurveDB] = @LearningCurves,
[bitUser_InfantToddlerDB] = @InfantToddler,
[bitUser_DataNoTel] = @UserDataNoTel,
[bitUser_DataNoPost] =@UserDataNoMail,
[bitUser_DataNoEmail]=@UserDataNoEmail,
[LastUpdated] = GetDate(),
[id_PSMH_MembersHear] = @MemberHear,
[ciboodleId] = @ciboodleId,
[UpdateSource] = @UpdateSource,
[bitUser_MonthlyOptIn] = @MonthlyOptIn,
[SOURCE] = @SOURCE
WHERE [emailMIL] = @EmailMIL
SET @MEMBERSHIP = (select MEMBERSHIP from [tblPSMH_Members] where = @Email)
END
ELSE if(@NORMALINSERT = 1)
BEGIN
DECLARE @CurrentDate datetime
SET @CurrentDatE = getdate()
INSERT INTO [dbo].[tblPSMH_Members]
([id_Category]
,[TITLE]
,[FORENAME]
,[SURNAME]
,[SEX]
,[JOBTITLE]
,[AD1]
,[AD2]
,[AD3]
,[TOWN]
,[COUNTY]
,[POSTCODE]
,[COUNTRY]
,[HomeOrWork]
,[WORKAD1]
,[WORKAD2]
,[WORKAD3]
,[WORKTOWN]
,[WORKCOUNTY]
,[WORKPOSTCODE]
,[WORKCOUNTRY]
,
,[MOBILE]
,[WORKTEL]
,[WORKMOB]
,[bitUser_MailListIP]
,[bitUser_MailListMilupaCRM]
,[bitStudyMIL]
,[bitProductMIL]
,[bitUser_DataNoTel]
,[bitUser_DataNoPost]
,[bitUser_DataNoEmail]
,[DateAdded]
,[id_PSMH_MembersHear]
,[JobType]
,[LastUpdated]
,[ciboodleId]
,[bitUser_MailListIP_options]
,[bitUser_LearningCurveDB]
,[bitUser_InfantToddlerDB]
,[UpdateSource]
,
,[emailMIL]
,[bitUser_MonthlyOptIn]
,[SOURCE])
VALUES
(11,
@Title,
@Forename,
@Surname,
@Gender,
@JobTitle,
@PrivateAddressLine1,
@PrivateAddressLine2,
@PrivateAddressLine3,
@PrivateTownCity,
@PrivateCounty,
@PrivatePostcode,
@PrivateCountry,
@HomeOrWork,
@WorkAddressLine1,
@WorkAddressLine2,
@WorkAddressLine3,
@WorkTownCity,
@WorkCounty,
@WorkPostcode,
@WorkCountry,
@PrivatePhone,
@PrivateMobile,
@WorkPhone,
@WorkMobile,
--@InPractice,
--@Aptamil,
CASE @InPractice
WHEN NULL THEN 0
ELSE @InPractice
END,
CASE @Aptamil
WHEN NULL THEN 0
ELSE @Aptamil
END,
CASE @Aptamil
WHEN 1 THEN @StudyDays
ELSE 0
END,
CASE @Aptamil
WHEN 1 THEN @ProductUpdates
ELSE 0
END,
@UserDataNoTel,
@UserDataNoMail,
@UserDataNoEmail,
@CurrentDate,
@MemberHear,
@JobType,
@CurrentDate,
@ciboodleId,
@InPracticeOptions,
@LearningCurves,
@InfantToddler,
@UpdateSource,
@Email,
@EmailMIL,
@MonthlyOptIn,
@SOURCE
)
SET @MEMBERSHIP = SCOPE_IDENTITY()
END
-- Need to update other CRM tables
IF(@Aptamil = 1)
BEGIN
DELETE FROM tblUserCategory WHERE id_User = @MEMBERSHIP;
IF(@StudyDays = 1)
BEGIN
INSERT INTO tblUserCategory (id_User, id_Category) VALUES (@MEMBERSHIP, 106)
END
IF(@ProductUpdates = 1)
BEGIN
INSERT INTO tblUserCategory (id_User, id_Category) VALUES (@MEMBERSHIP, 107)
END
END
ELSE if(@InPractice = 1)
BEGIN
UPDATE live_phpengine_users set email_alerts3 = @InPracticeOptions WHERE id_user_extern3 = @MEMBERSHIP
END
IF( @UpdateSource = 'CIBOODLE' )
BEGIN
insert into
tblPSMH_MembersUpdated
(
id_PSMH_MembersUpdatedBy,
MEMBERSHIP
)
values
(
6,
@MEMBERSHIP
)
END
January 19, 2011 at 6:08 am
I don't have the energy to work through all the attached code, but the trigger has some conditions in it. How sure are you that those are not failing to match what is inserted?
I have a couple of suggestions depending on what you can change and call.
1) Print a PRINT statement in the trigger, and invoke one of the calling procedures by hand in management studio (or query analyzer if you are really running that old of system), look and see if it got called.
2) Rig the procedure to ALSO insert unconditionally into a log table solely for that purpose, so you can see for sure when it is called.
3) It is possible to turn triggers off -- is there any chance some code is doing so? (see ENABLE TRIGGER, DISABLE TRIGGER)
The source of the connection should not matter, the trigger is a logical thing, not connection specific. Attack it outside of the mutli-system environment, rig something up. The problem will show up. My bet would be somehow it is on the where predicate. Example -- could any of those fields by null instead of zero? If any of those three is null the insert will not happen the way you have it written.
January 19, 2011 at 2:24 pm
For the sake of my OCD, combine the two inserts in the trigger:
ALTER trigger [dbo].[InsertJoinWebsiteEVS]
on [dbo].[Members]
for insert
as
SET NOCOUNT ON
insert into EVSActions ( Membership, EVSActionTypeID, ActionDate )
select
Inserted.Membership,
case when Inserted.bitUser_MonthlyOptIn = 1 then 1 else 2 end,
getdate()
from
Inserted
where
Inserted.bitUser_MailListIP = 1
and Inserted.Deleted = 0
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
January 20, 2011 at 3:50 pm
Aside from taking toddasd's suggestion to combine the 2 different insert statements into 1, which I heartily agree with, I've worked with SQL 2000 and 2005 with MS Access and other web interfaces for years. I've never seen a case where a trigger won't fire due to the application doing the insert or update. There's always something in either the trigger or the expected data that is the problem.
For example, if you always expect a column in the inserted record to either have 1 or 0 and the application is either setting it to NULL or letting it default to NULL, then neither of your predicates will test true.
Add to the trigger to insert into another table if you find NULL instead of 1 or 0.
Todd Fifield
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply