We help IT Professionals succeed at work.

Getting Trigger Happy with SQL Server 2000

176 Views
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)

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




Comment
Watch Question

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

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

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.