Getting Trigger Happy with SQL Server 2000
Posted on 2007-10-01
I am creating an insert trigger in SQL Server 2000 that works if I insert a new user through the query analyzer but when a new user is created through the web app I get a Foreign Key constraint error. I'm new to triggers so I'm hoping the fix will be straightfoward or maybe something I overlooked.
Is there a way to create a trigger that fires after an insert is executed so it won't interfear with the constaints?
Here is my table structure:
table: APPUSERS (tie between a particular app in this case the fifth one and the user id when on that app)
AU_ID, AU_APP_ID, AU_USER_ID,
----- ------------- -----------
466 5 635
467 5 659
856 5 1117
Here is the trigger:
CREATE trigger trig_INSERT_AppUsers
IF update (AU_ID)
DECLARE @appid as int
SELECT @appid = (SELECT AU_APP_ID FROM INSERTED)
IF (@appid = 5)
DECLARE @USR_RET_ID as int
SELECT @USR_RET_ID = (SELECT AU_ID FROM INSERTED)
INSERT INTO appuserprivs
Here is the error that is being thrown:
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_AppUserRoles_AppUsers'.
The conflict occurred in database 'OConnordata', table 'AppUsers', column 'AU_ID'.
The problem is in creating new users. If I don't use the trigger, I can create one just fine but using the trigger causes the FK error.
I'm using coldfusion MX 7 on Windows OS and MS SQL Server 2000.