Getting Trigger Happy with SQL Server 2000

Posted on 2007-10-01
Last Modified: 2010-03-20
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.


Question by:FastEddie___
    LVL 68

    Accepted Solution

    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*.
    LVL 16

    Assisted Solution

    In Order to add user privilages to a user, the user must exist first, so insert new user first (AppUsers)

    Author Comment

    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?


    LVL 68

    Expert Comment

    Sorry, I have just been too busy to get back to this for any folow-up / details.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now