Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2011
    Posts
    5

    Unanswered: Triggers, Stored Procedures, & MS Access UI's

    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

    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]
    ,[TEL]
    ,[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]
    ,[email]
    ,[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()

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Why are you doing this "and Inserted.Deleted = 0". Check where conditions. All must be met. Essentially you are using triggers to enforce business rules and data integrity.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Your trigger is on dbo.Members, while your procedure is inserting into dbo.tblPSMH_Members.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jan 2011
    Posts
    5
    Exactly! We had to enforce business rules with triggers, since there is a MS Access front-end UI that directly connects to the members table that needs to have user activity rtracked, as well. Since this trigger was the solution opted for by the client, it necessitated the removal of code in the website & stored procedure to avoid duplicate action tracking entries.

    Are you saying that the criteria in the where clause might be invalid?

    - O8

  5. #5
    Join Date
    Jan 2011
    Posts
    5
    Sorry, rdjabarov, I was trying to clean up the sql to avoid character limits. The table with the triggers & being updated by the stored procedure is tblPSMH_Members.

    - O8

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can construct and script a profiler trace that will only capture INSERTs into the table. When selecting events make sure to include RPC calls. In the columns include HostName along with loginname and application name, to be able to distinguish where the INSERT is coming from. Once the trace is captured and collected, you can examine the statements that are coming from Access UI to see what gives. As per procedure - unless Deleted is defined with DEFAULT constraint, your WHERE clause in the INSERT trigger will always return 0 rows, meaning 0 rows will be affected, because Deleted will have NULL in the INSERTED virtual table.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Any chance of bumping up to a version of SQL that supports CDC?

    Assuming the answer is no, I would suggest changing your approach to abstracting data access through stored procedures AND ONLY stored procedures, then homebrewing whatever historical records you want to track. Triggers have a way of creating maintainability/predictability issues further down the line.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Teddy View Post
    Any chance of bumping up to a version of SQL that supports CDC?

    Assuming the answer is no, I would suggest changing your approach to abstracting data access through stored procedures AND ONLY stored procedures, then homebrewing whatever historical records you want to track. Triggers have a way of creating maintainability/predictability issues further down the line.
    All good ideas, but I think the OP is operating within the customer's constraints. Apparently they love their little MSAccess app, and want to make sure that the vendor (the OP) can cope with the task of tracking the activity generated by it as well.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by RealityMasque View Post
    Don't data modification events happen when stored procedures & MS Access UI's insert into a table?
    Yes. Triggers fire regardless of the initiator of the transaction. There is nothing in that trigger that would prevent it from firing on each insert, unless the application specifically disables the trigger first (unlikely).

    You said there were two other triggers. What are they, and in what order to the three triggers fire?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by blindman View Post
    Yes. Triggers fire regardless of the initiator of the transaction. There is nothing in that trigger that would prevent it from firing on each insert, unless the application specifically disables the trigger first (unlikely).

    You said there were two other triggers. What are they, and in what order to the three triggers fire?
    If I understand correctly this is a For Insert trigger? But the Inserted.Deleted = 0 would more likely be true for an Update trigger where the record is deleted first and a new record inserted. I believe rdjabarov also pointed this out.

  11. #11
    Join Date
    Jan 2011
    Posts
    5
    corncrowe: yeah, it's a For Insert trigger. The Inserted.Deleted = 0 was put in to handle when new members are added via the sproc or the Access UI but are more for record keeping that they exist, but are initially "deleted". Given that the sproc doesn't insert that field, & the field defaults to 0 (false) in the table, it's likely that the trigger is getting a null, so the deleted = 0 criteria is causing the problem (I'm doing some tests on this with Access UI people now).

    blindman: as far as I know, the sproc doesn't disable the trigger before doing its work, & I'm fairly certain that the Access UI doesn't either. Of course, I inherited this project well after both were developed. In terms of the order the triggers fire, I couldn't say. Is there a way to tell the DB to order them? & for completeness, below are the other 2 triggers. As everyone can see, I added the same Insert.Deleted = 0 criteria. I'm hoping that changing that will resolve the issue.

    - O8


    insert into EVSActions ( Membership, EVSActionTypeID, ActionDate )
    select
    Inserted.Membership,
    4,
    getdate()
    from
    Inserted
    inner join Deleted on Deleted.Membership = Inserted.Membership
    where
    ( Deleted.email is null or Deleted.email = '' )
    and ( Inserted.email is not null and len( Inserted.email ) > 5 )
    and Inserted.bitUser_MailListIP = 1
    and Inserted.Deleted = 0


    insert into EVSActions ( Membership, EVSActionTypeID, ActionDate )
    select
    Inserted.Membership,
    3,
    getdate()
    from
    Inserted
    inner join Deleted on Deleted.Membership = Inserted.Membership
    where
    Inserted.bitUser_MailListIP = 1
    and Inserted.Deleted = 0
    and
    (
    Inserted.Title <> Deleted.Title
    or Inserted.Forename <> Deleted.Forename
    or Inserted.Surname <> Deleted.Surname
    or Inserted.JobTitle <> Deleted.JobTitle
    or Inserted.JobType <> Deleted.JobType
    or Inserted.Tel <> Deleted.Tel
    or Inserted.Mobile <> Deleted.Mobile
    or Inserted.Fax <> Deleted.Fax
    or Inserted.Ad1 <> Deleted.Ad1
    or Inserted.Ad2 <> Deleted.Ad2
    or Inserted.Town <> Deleted.Town
    or Inserted.County <> Deleted.County
    or Inserted.Postcode <> Deleted.Postcode
    or Inserted.Email <> Deleted.Email
    or Inserted.NvcUser_Password <> Deleted.NvcUser_Password
    or Inserted.BitUser_MonthlyOptIn <> Deleted.BitUser_MonthlyOptIn
    or Inserted.BitUser_EmailHtml <> Deleted.BitUser_EmailHtml
    or Inserted.BitIPNewsletterByEmail <> Deleted.BitIPNewsletterByEmail
    or Inserted.BitIPNewsletterByPrint <> Deleted.BitIPNewsletterByPrint
    or Inserted.BitUser_DataNoTel <> Deleted.BitUser_DataNoTel
    or Inserted.BitUser_DataNoPost <> Deleted.BitUser_DataNoPost
    or Inserted.BitUser_DataNoEmail <> Deleted.BitUser_DataNoEmail
    or Inserted.BitUser_DataNoSMS <> Deleted.BitUser_DataNoSMS
    )

  12. #12
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    I'd consider changing the trigger to an for insert,update. The code snippet in your trigger that checks for changes can be simply a check of the update trigger and then the Inserted.Deleted != 0 would be valid.

  13. #13
    Join Date
    Jan 2011
    Posts
    5
    ok, I changed the trigger as below (yes, the criteria changed from the bitUser_MonthlyOptIn flag to the email being provided). So far, this worked with the MS Access UI, & I'll be able to test the sproc by tomorrow.

    Thanks for your help, & I'll let y'all know if it works in the sproc case, too.

    - O8


    ALTER trigger [dbo].[InsertJoinInPracticeEVS]

    on [dbo].[tblPSMH_Members]

    for insert

    as

    SET NOCOUNT ON

    -- in practice registration with email (full)
    insert into EVSActions ( Membership, EVSActionTypeID, ActionDate )
    select
    Inserted.Membership,
    1,
    getdate()
    from
    Inserted
    where
    Inserted.bitUser_MailListIP = 1
    and
    (
    Inserted.email is not null
    and Inserted.email <> ''
    )
    and
    (
    Inserted.Deleted = 0
    or Inserted.Deleted is null
    )

    -- in practice registration with NO email (partial)
    insert into EVSActions ( Membership, EVSActionTypeID, ActionDate )
    select
    Inserted.Membership,
    2,
    getdate()
    from
    Inserted
    where
    Inserted.bitUser_MailListIP = 1
    and
    (
    Inserted.email is null
    or Inserted.email = ''
    )
    and
    (
    Inserted.Deleted = 0
    or Inserted.Deleted is null
    )

  14. #14
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by RealityMasque View Post
    ... I'll be able to test the sproc by tomorrow.
    For what it's worth there is an easy way to test triggers without executing the underlying sources. Write code to create a temp table called inserted or deleted, insert some values, and execute dml.

    -- puesdo code

    create temp table inserted (col1 datatype, col2 datatype, etc.)

    select i.col1,i.col2
    from inserted i
    where some condition applies

    Good luck!

    Jon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •