#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2011
    Posts
    3
    Rep Power
    0

    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
  3. No Profile Picture
    q97
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Apr 2005
    Location
    Brisvegas
    Posts
    439
    Rep Power
    115
    Unless I've missed something, you've put your trigger on [dbo].[Members]
    but your stored procedure inserts into [dbo].[tblPSMH_Members]

    They are not the same table...?

    As a quick rundown on triggers - triggers attach to the object (table) and they don't care one bit what program etc you use to work with the database. The DBMS only cares that it has some defined work to do when (or even before) some data events occur. Do remember that insert only works for inserting a new row, and will not fire for an update. But you can chain the event types together if you want the same trigger to fire for the different event types. eg for insert, update, delete...

    Comments on this post

    • nofriends agrees : well spoted
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2011
    Posts
    3
    Rep Power
    0
    Sorry about that -- I was trying to clean up the SQL a bit, but only got halfway through. You're right, it's the same table: tblPSMH_Members. & as you mention (& I read in MS's documentation), triggers react to data modification events, regardless of the source of said event.

    The trigger only needs the insert, as it's for the purpose of tracking when new members are added to the table (i.e., newly joining members). On another forum, there was a hypothesis that the Inserted.Deleted = 0 in the where clause of the trigger could be the culprit. The website provides all values, including the Deleted field, while the stored procedure does not, & while I have no idea how the MS Access UI was constructed, I'm fairly certain that only the required values are passed from the UI (of which Deleted is only passed when members stop being members).

    I'm going to test that theory, but is there anything else that might be an issue?

    Thanks for the info, btw...

    - O8
  6. #4
  7. No Profile Picture
    q97
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Apr 2005
    Location
    Brisvegas
    Posts
    439
    Rep Power
    115
    I'd be tempted to work backwards and get your trigger to display select * from inserted and see what values you get...
  8. #5
  9. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2011
    Posts
    3
    Rep Power
    0
    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
    )

Similar Threads

  1. Access 2000 and SQL Stored Procedure
    By Knight in forum Microsoft Access Help
    Replies: 8
    Last Post: July 25th, 2007, 08:18 AM
  2. Stored Procedures!
    By roydelton in forum ASP Development
    Replies: 9
    Last Post: June 5th, 2006, 05:33 AM
  3. Stored Procedures / Triggers
    By surma884 in forum SQL Development
    Replies: 1
    Last Post: June 2nd, 2006, 02:36 PM
  4. Timing Issues with Stored Procedures
    By h-kay in forum Microsoft SQL Server
    Replies: 2
    Last Post: August 15th, 2005, 03:51 AM
  5. Learning how to write Stored Procedures
    By Steve Schofield in forum SQL Development
    Replies: 0
    Last Post: October 10th, 2000, 06:01 PM

IMN logo majestic logo threadwatch logo seochat tools logo