Getting Trigger Happy with SQL Server 2000

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)

-----       -------------    -----------
466           5                        635
467           5                     659
856           5                     1117

Here is the trigger:

CREATE trigger trig_INSERT_AppUsers
on dbo.appusers
for  insert

IF update (AU_ID)


             DECLARE @appid as int

         IF (@appid = 5)

            DECLARE @USR_RET_ID as int
            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.


Who is Participating?
Scott PletcherConnect With a Mentor Senior DBACommented:
Looks like the value for AU_ID needs to be inserted into table AppUserRoles before being inserted into appusers.

Btw, you need to change your trigger code to handle multiple rows being inserted in one statement.  SQL triggers only fire *once per statement, not per row*.
SQL_SERVER_DBAConnect With a Mentor Commented:
In Order to add user privilages to a user, the user must exist first, so insert new user first (AppUsers)
FastEddie___Author Commented:
Ok so what I think you are saying is that I need to let the user be created first and then run the trigger. Does that lend itself to an "AFTER trigger" instead of just a regular trigger?

What I'm trying to do is when a colum gets updated. Check it to see if has the value of 5. If so then insert a record into another table.  

Scott, what did you mean when you said I needed to change the trigger to handle multiple rows?


Scott PletcherSenior DBACommented:
Sorry, I have just been too busy to get back to this for any folow-up / details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.