Solved

SQL Trigger on Insert

Posted on 2009-07-08
18
1,184 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

0
Comment
Question by:vb7guy
  • 6
  • 5
  • 4
  • +2
18 Comments
 
LVL 40

Expert Comment

by:RQuadling
ID: 24807246
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
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 125 total points
ID: 24807462
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
 
LVL 41

Expert Comment

by:pcelba
ID: 24807629
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
 
LVL 41

Expert Comment

by:pcelba
ID: 24807640
BTW, the audit table has different schema. Aren't you looking somewhere else?
0
 

Author Comment

by:vb7guy
ID: 24808750
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
 

Author Comment

by:vb7guy
ID: 24808788
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
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 125 total points
ID: 24808812
>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
 

Author Comment

by:vb7guy
ID: 24808911
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 40

Assisted Solution

by:RQuadling
RQuadling earned 125 total points
ID: 24810988
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
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 125 total points
ID: 24812724
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
 

Accepted Solution

by:
vb7guy earned 0 total points
ID: 24813700
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
 
LVL 41

Expert Comment

by:pcelba
ID: 24814011
LOL, for some reason we forgot to ask what is the exact code which does not fire the trigger...
0
 

Author Comment

by:vb7guy
ID: 24814082
Thank you again. This was a good learning experience.
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 24821642
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
 
LVL 40

Expert Comment

by:RQuadling
ID: 24821663
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
 

Author Comment

by:vb7guy
ID: 24822054
That's why I have given equal points.
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 24822089
Sorry. I got the wrong end of the stick completely. My bad. Proceed as you were. Nothing to see here....
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Stored procedure 4 26
MS SQL page split per second is high 19 64
SQL Update Query 23 79
Has anyone used domo? 1 39
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Creating and Managing Databases with phpMyAdmin in cPanel.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 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

23 Experts available now in Live!

Get 1:1 Help Now