[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Trigger on Insert

Posted on 2009-07-08
18
Medium Priority
?
1,265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +2
18 Comments
 
LVL 40

Expert Comment

by:Richard Quadling
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 500 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 43

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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 43

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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 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
 
LVL 40

Assisted Solution

by:Richard Quadling
Richard Quadling earned 500 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 43

Assisted Solution

by:pcelba
pcelba earned 500 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 43

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:Richard Quadling
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:Richard Quadling
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:Richard Quadling
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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

649 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