Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Location
    Boise, Idaho
    Posts
    9

    Unanswered: Before Delete Trigger?

    I have a database that will be used as the back end of a distributed application that holds information based on application users. The application needs to log on and see if there are updates for the user. My current thoughts on this are that the application will log in and check a Date column named [modified] in the users table (I am not worried about what individual changes have occured, but more if anything has changed). To implement this I have now put Insert, Update triggers that use the tables relationships to track which users need to be updated on the tables that need to be watched... they look something like this:

    CREATE TRIGGER MOD_UP_INS_GROUPS
    ON dbo.Groups
    FOR INSERT, UPDATE
    AS
    SET NOCOUNT ON
    DECLARE @IDVar1 as int
    SET @IDVar1 = (SELECT GroupID FROM inserted)
    UPDATE Users
    SET Modified = GetDate()
    WHERE (AccountName IN
    (SELECT DISTINCT dbo.Users.AccountName
    FROM dbo.Groups INNER JOIN
    dbo.GroupUserDetail ON dbo.Groups.GroupID = dbo.GroupUserDetail.GroupID INNER JOIN
    dbo.Users ON dbo.GroupUserDetail.AccountName = dbo.Users.AccountName
    WHERE (dbo.Groups.GroupID = @IDVar1)))

    This appears to be working great... however the Delete Trigger is where my problems start... I can not use the above trigger (with deleted in place of inserted) because it appears the delete action takes place prior to the Delete Trigger and with the referential deletes, etc. The path to the user is lost before I can track it with the delete trigger. Is there a way to make a BEFORE DELETE TRIGGER... or any other thoughts would be helpfull.

    Thank You,
    Kent

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Before Delete Trigger?

    Do Instead of Trigger.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not sure I'm following...but you can code an INSTEAD OF trigger...look it up in BOL...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Dec 2003
    Location
    Boise, Idaho
    Posts
    9

    Angry Re: Before Delete Trigger?

    I tried a Instead of Trigger but I had the following error:

    Cannot ALTER INSTEAD OF DELETE or UPDATE TRIGGER 'MY TRIGGER NAME' on table 'dbo.Group' because the table has a FOREIGN KEY with cascaded DELETE or UPDATE.

    Doing a little reading I have found I can't define this trigger on tables with foreign key relationships with cascading deletes.

    The other problem I thought of with this type of trigger... is how do you let an outside programs sql delete requests continue with this trigger?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well...can you simply explain what your goal is, in business terms...

    I'm having a hard time seeing what you're trying to do..

    Also, you got other problems

    Code:
    SET @IDVar1 = (SELECT GroupID FROM inserted)
    You do know that inserted may have many rows...so doing that will give you the last value in the result set....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Dec 2003
    Location
    Boise, Idaho
    Posts
    9
    First of all thanks for your replies:

    The Database is the backend of a Client-Server Application that assigns Startup Scripts for Company Programs to individual users within the company (Based on Windows Login Names that are stored in the users table). It does this in a method similar to the Windows Server Environment where you assign individual Company Programs to a Group then assign a group to a user or a user to a group.

    The front end that the users see needs to be able to connect to the database and determine if anything has changed, and if so, update itself with the new settings whether it be the user has been added\deleted from a group or if an actual Company Program Startup Script has changed.

    My attempted solution to this problem was to create a modified column in the users table. When a table is modified that effects a user or users the modified column for the user or users in question would be updated with the current date (getdate()). The front end then accessess the database and compares it's last updated date with the date in the users.modified column to determine if it needs to update. With the update, Insert Triggers I have been able to accomplish this very nice. However, the delete trigger causes problems because lets say a group is deleted.... the group is deleted then referential updates delete the users who where assigned to that group in a groupdetails table then I am unable to track which users need to be modified...

    The front end does not contain a database... but rather stores items in an ini file and the registry, therefore replication is not an option. The other thing is clients are not always connected to the network so the settings are stored on the local machine for the individual users.

    I hope this is clearer... thanks again.

Posting Permissions

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