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
FOR INSERT, UPDATE
SET NOCOUNT ON
DECLARE @IDVar1 as int
SET @IDVar1 = (SELECT GroupID FROM inserted)
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.
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.