Solved

After Insert Trigger not Firing

Posted on 2011-03-09
5
528 Views
Last Modified: 2012-05-11
Hallo all

I have the trigger below..
After Insert of an Article : the trigger will fail to update the current Article.
It Only update an article before the current Transaction

i.e
Article 1 : - Time 45
Article 2 :- Time 50

If I run insert with Article 3, only the Article 1 and 2 will be updated the current Article that
Initated the transaction will not be update,
How can I get around this ?

Thanks in Advance

USE [Prise]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

      ALTER TRIGGER [dbo].[controlPrise]
            ON  [dbo].[Cheap_Preis_H]
                  AFTER INSERT
      AS
            

                  BEGIN
                        -- SET NOCOUNT ON added to prevent extra result sets from
                        -- interfering with SELECT statements.
                        SET NOCOUNT ON;                              
                        
                                    
                                    UPDATE [TST].[dbo].[Cheap_Preis_H]
                                    SET   State = 150
                                                
                                    WHERE State  =       50
                  END
0
Comment
Question by:ZURINET
  • 3
  • 2
5 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35083932
you have to refer to INSERTED table to know which rows have been "updated".

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

      ALTER TRIGGER [dbo].[controlPrise] 
            ON  [dbo].[Cheap_Preis_H] 
                  AFTER INSERT
      AS 
            

                  BEGIN
                        -- SET NOCOUNT ON added to prevent extra result sets from
                        -- interfering with SELECT statements.
                        SET NOCOUNT ON;                              
                        
                                    
                                    UPDATE [TST].[dbo].[Cheap_Preis_H]
                                    SET   State = 150
                                                
                                    WHERE Primary_Key_Field in ( SELECT primary_key_field FROM INSERTED )
                  END

Open in new window

0
 

Author Comment

by:ZURINET
ID: 35092639
Hoi AngelIII
This solution did not solve my problem..

According to Profiler Log.. The real insert occurs after the trigger..

I am still looking for a solution
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35092769
let me show this test script and it's output:
USE TEMPDB
go
set nocount on
go
create table [dbo].[Cheap_Preis_H] ( Primary_Key_Field int, data varchar(10), state int )
go

      CREATE TRIGGER [dbo].[controlPrise]
            ON  [dbo].[Cheap_Preis_H]
                  AFTER INSERT
      AS
            

                 
                  BEGIN
                        -- SET NOCOUNT ON added to prevent extra result sets from
                        -- interfering with SELECT statements.
                        SET NOCOUNT ON;                              
                        
                                    
                                    UPDATE [dbo].[Cheap_Preis_H]
                                    SET   State = 150                                                
                                    WHERE Primary_Key_Field in ( SELECT primary_key_field FROM INSERTED )
                  END
go
insert into [dbo].[Cheap_Preis_H] (Primary_Key_Field, data, state ) values ( 1, 'row 1' , 10)
go
select * from [dbo].[Cheap_Preis_H] 
go
update [dbo].[Cheap_Preis_H] 
  set state = 10
where Primary_Key_Field = 1
go
select * from [dbo].[Cheap_Preis_H] 
go
insert into [dbo].[Cheap_Preis_H] (Primary_Key_Field, data ) values ( 2, 'row 2' )
go
select * from [dbo].[Cheap_Preis_H] 

go
drop table [dbo].[Cheap_Preis_H] 

Open in new window


result:
Primary_Key_Field	data	state
1	row 1	150

Primary_Key_Field	data	state
1	row 1	10

Primary_Key_Field	data	state
1	row 1	10
2	row 2	150

Open in new window


which seem to be the proof that the after insert trigger does update the state field for ONLY the row that has been inserted ...
0
 

Author Comment

by:ZURINET
ID: 35092803
Hi

I recorded the same sucess if I execute the code in Sql Analyser..
However if the Processe , i.e if the source file is Inserted or written by BizTalk server to the SQL DB
The query will not update the DB.

May be the problem lies on how the BizTalk Sql adapter is  inserting data into the DB.

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35092852
as long as the process would not do a BULK INSERT (aka from file), the triggers must all fire.
you might want to perform a sql profiler on the database to monitor the sql run against the db, to see what is happening (you shall also see the sql from the trigger running ...)
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql query 12 67
SQL Select - Finding chars in a column 2 57
ms sql + top 1 for each customer 3 43
Getting max record but maybe not use Group BY 2 16
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

896 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

14 Experts available now in Live!

Get 1:1 Help Now