SQL Trigger on Insert

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

vb7guyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Richard QuadlingSenior Software DeveloperCommented:
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.
0
BrandonGalderisiCommented:
One thing you definitely want to do is specify your insert column list.  


ex.

 INSERT INTO dbo.AuditUsersOnInsert_Temp (guid_col,mod_date,userid,registrantid,[status],accualstatus,housestatus,oprstatusoverride)
  SELECT  NEWID(), 
	GETDATE(),
	USERid,
    RegistrantId,
    [Status],
    ActualStatus,
    HouseStatus,   
    OPRStatusOverride   
    FROM inserted 

Open in new window

0
pcelbaCommented:
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...
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

pcelbaCommented:
BTW, the audit table has different schema. Aren't you looking somewhere else?
0
vb7guyAuthor 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
0
vb7guyAuthor 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.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>and I don't have to specify the fields inside INSERT Statement.
you should do so, though.
the day you add a field to either table will make your trigger still work, so it can be modified accordingly a bit later without breaking any user interface...


>I'm using SQL 2005
right-click table, open table, and enter the data?
do you "change row", which will actually run the INSERT ?

I am sure you are missing something "obvious" like that, or a commit with autocommit off, and checking from another session ...
0
vb7guyAuthor 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.  
0
Richard QuadlingSenior Software DeveloperCommented:
What constraints do you have on the audit table? Duplicates being discarded?

What messages do you get when you run a simple insert on the users table using Query Analyzer or Enterprise Manager.

I want to see the messages as I am expecting a certain result.

0
pcelbaCommented:
I've been asking to add some PRINT statement into the trigger in answer ID:24807629. Did you try it already?

I've been asking about the different schema, as well. I know the different schema is allowed but my question was pointed to a possibility of checking inserted records in PublicFacing.AuditUsersOnInsert_Temp instead of dbo.AuditUsersOnInsert_Temp.

How do you check the audit records are not there? Aren't you simply looking at the end of the table? Did you count audit table records before and after the trigger execution (or even inside the trigger)?

We don't know your environment and you are not telling everything.

Are you sure the trigger is not disabled?
0
vb7guyAuthor Commented:
Yes, I did try Print Statement.  That didn't help.
Trigger is not disabled.

I found out that Trigger is only firing when I manualy insert the record either by opening a table or run a Insert statement.  However, I was using Import/Export Wizard to insert data into my users table.  After talking a coworker I found that Import/Export wizard uses non transactional method to insert records (bulk copy).  Therefore, "inserted" table would not be used and trigger will not fire.

Anyways, This trigger is working.  Thank you all for your comments.  I will accept multiple question, even though the solutions provided had nothing to do with the problem.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
LOL, for some reason we forgot to ask what is the exact code which does not fire the trigger...
0
vb7guyAuthor Commented:
Thank you again. This was a good learning experience.
0
Richard QuadlingSenior Software DeveloperCommented:
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
0
Richard QuadlingSenior Software DeveloperCommented:
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.

0
vb7guyAuthor Commented:
That's why I have given equal points.
0
Richard QuadlingSenior Software DeveloperCommented:
Sorry. I got the wrong end of the stick completely. My bad. Proceed as you were. Nothing to see here....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.