[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

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)

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
on dbo.appusers
for  insert
AS

IF update (AU_ID)

BEGIN

             DECLARE @appid as int
             SELECT @appid = (SELECT AU_APP_ID FROM INSERTED)

         IF (@appid = 5)
      BEGIN

            DECLARE @USR_RET_ID as int
            SELECT @USR_RET_ID = (SELECT AU_ID FROM INSERTED)
      
            INSERT INTO appuserprivs
            (
                 AUP_AU_ID,
                 AUP_AP_ID,
                                AUP_Allowed,
                                AUP_CreatedDate,
                                AUP_CreatedBy
            )
            VALUES
            (
               @USR_RET_ID,
                        57,
                                0,
                                GetDate(),
                                0
             )
      END
 END


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.

-Eddie




0
FastEddie___
Asked:
FastEddie___
  • 2
2 Solutions
 
Scott PletcherSenior 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*.
0
 
SQL_SERVER_DBACommented:
In Order to add user privilages to a user, the user must exist first, so insert new user first (AppUsers)
0
 
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?

-Eddie

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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now