We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL Trigger on Insert

Medium Priority
1,327 Views
Last Modified: 2012-05-07
I have a trigger which is suppose to fire up when ever a records is being insert.  This trigger is design to capture data into an Audit table which I just create.  But for some reasons when I insert records into my "Users" table it does not insert any records into "AuditUsersOnInsert_Temp".  I don't know what I'm doing wrong.

I'm using SQL 2005
/****** Object:  Trigger [PublicFacing].[LogINSERTInAuditUsers]    Script Date: 07/08/2009 15:10:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [PublicFacing].[LogINSERTInAuditUsers]
ON [PublicFacing].[Users]
FOR INSERT
AS
BEGIN
 
 INSERT INTO dbo.AuditUsersOnInsert_Temp
  SELECT  NEWID(), 
	GETDATE(),
	USERid,
    RegistrantId,
    [Status],
    ActualStatus,
    HouseStatus,   
    OPRStatusOverride   
    FROM inserted 
END

Open in new window

Comment
Watch Question

Richard QuadlingSenior Software Developer

Commented:
Can you use Enterprise Manager and create a new query which manually inserts a new row into the Users table.

Check the messages tab to see if anything went wonky.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
Try SQL Profiler or simply put the

print 'LogINSERTInAuditUsers trigger'

statement just before the INSERT just to see if the trigger is fired or not...
CERTIFIED EXPERT

Commented:
BTW, the audit table has different schema. Aren't you looking somewhere else?

Author

Commented:
The Syntax on the "INSERT" satement in this trigger is correct.  Because If try to the run the following query manuall and can substitude "FROM inserted" to "FROM USERS"   it works fine and I don't have to specify the fields inside INSERT Statement.

What I'm trying to do is to keep track of what's being inserted into the USERS table.  I would rather have this as part of the trigger so that it runs automatically.



INSERT INTO dbo.AuditUsersOnInsert_Temp
  SELECT  NEWID(),
      GETDATE(),
      USERid,
    RegistrantId,
    [Status],
    ActualStatus,
    HouseStatus,  
    OPRStatusOverride  
    FROM USERS

Author

Commented:
Having different schema has nothing to do why it's not working.  We do have an update trigger on the same table and the audit table is in different schema, it works just fine.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Ok, i guess I'll add fields that I want to insert into my Audit table.

I have tried to insert several rows into the users table but the Triger doesn't seems to insert anything into the Audit table.

I don't think Commit is the issue. we have other trigger (update,delete) in our database tat work just fine.  This is my first time running a trigger on insert.  
Richard QuadlingSenior Software Developer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
LOL, for some reason we forgot to ask what is the exact code which does not fire the trigger...

Author

Commented:
Thank you again. This was a good learning experience.
Richard QuadlingSenior Software Developer

Commented:
You CAN fire triggers for bcp / BULK INSERT.

For bcp (http://msdn.microsoft.com/en-us/library/ms162802.aspx), look for -h FIRE_TRIGGERS

For BULK INSERT (http://msdn.microsoft.com/en-us/library/ms188365.aspx), look for FIRE_TRIGGERS
Richard QuadlingSenior Software Developer

Commented:
In #24807246, I asked you to run a manual insert on the users table and see what the messages were. This would have output 2 messages, 1 row(s) affected times 2 (hopefully).

pcelba also asked for debugging code to be added - essentially providing proof that the trigger is working when data is inserted.

Replies to these points, would have lead to us asking what code you were using.

Author

Commented:
That's why I have given equal points.
Richard QuadlingSenior Software Developer

Commented:
Sorry. I got the wrong end of the stick completely. My bad. Proceed as you were. Nothing to see here....
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.