Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Trigger not firirng in sql server
Add to briefcase

Trigger not firirng in sql server Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
selvakumar.sms
Posted Thursday, October 25, 2012 7:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:00 PM
Points: 34, Visits: 202
hi all

I have ON INSERT and UPDATE trigger for my table,if i update any column or inserting any rows in SSMS, trigger is working well, but i read data from file and insert in the table means trigger is not working, here i my trigger coding please give your suggestion.

ALTER TRIGGER [dbo].[tr_sync_GH_Account_Master_Transfer_Staging]
ON [dbo].[GH_Account_Master_Transfer_Staging]
FOR INSERT, UPDATE
AS
BEGIN
declare @seqno as int

SELECT @seqno = S.ExtractSequenceNo
FROM EPOSSDataExtractSequence S INNER JOIN EPOSSyncOrderMaster M
ON S.MasterCategory = M.MasterCategory
WHERE M.SyncTableName = 'GH_Account_Master_Transfer_Staging'


UPDATE GH_Account_Master_Transfer_Staging
SET SeqNo = @seqno + 1
FROM INSERTED
WHERE
GH_Account_Master_Transfer_Staging.SourceDocNo = INSERTED.SourceDocNo
AND GH_Account_Master_Transfer_Staging.SourceFiscalYear = INSERTED.SourceFiscalYear
AND GH_Account_Master_Transfer_Staging.SourceLocationCode = INSERTED.SourceLocationCode
END

Every experts was once a beginners!!! every pros was once an amateur
Post #1376990
GilaMonster
Posted Thursday, October 25, 2012 8:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 38,099, Visits: 30,392
How are you inserting from a file?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1377007
selvakumar.sms
Posted Thursday, October 25, 2012 8:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:00 PM
Points: 34, Visits: 202
no bulk insert , reading line by line and inserting in to the table using .net scheduler.

Every experts was once a beginners!!! every pros was once an amateur
Post #1377039
DiverKas
Posted Thursday, October 25, 2012 8:48 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:29 AM
Points: 244, Visits: 428
selvakumar.sms (10/25/2012)
hi all

I have ON INSERT and UPDATE trigger for my table,if i update any column or inserting any rows in SSMS, trigger is working well, but i read data from file and insert in the table means trigger is not working, here i my trigger coding please give your suggestion.

ALTER TRIGGER [dbo].[tr_sync_GH_Account_Master_Transfer_Staging]
ON [dbo].[GH_Account_Master_Transfer_Staging]
FOR INSERT, UPDATE
AS
BEGIN
declare @seqno as int

SELECT @seqno = S.ExtractSequenceNo
FROM EPOSSDataExtractSequence S INNER JOIN EPOSSyncOrderMaster M
ON S.MasterCategory = M.MasterCategory
WHERE M.SyncTableName = 'GH_Account_Master_Transfer_Staging'


UPDATE GH_Account_Master_Transfer_Staging
SET SeqNo = @seqno + 1
FROM INSERTED
WHERE
GH_Account_Master_Transfer_Staging.SourceDocNo = INSERTED.SourceDocNo
AND GH_Account_Master_Transfer_Staging.SourceFiscalYear = INSERTED.SourceFiscalYear
AND GH_Account_Master_Transfer_Staging.SourceLocationCode = INSERTED.SourceLocationCode
END


You DO realize that this will call the trigger recursively, since you are updating the table the update trigger is on? You would need to add checks for what column is getting updated to avoid that and you probably want an AFTER INSERT trigger.
Post #1377048
Lowell
Posted Thursday, October 25, 2012 8:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 11,792, Visits: 28,078
bulk insert does not automatically fire triggers unless you specifically set the flag (FIRE_TRIGGERS ).

I'm not sure your trigger is not designed to handle multiple rows correctly.
if i insert 100 rows for example, all 100 rows will have the same value in SeqNo . is that what you want, or did you want it to be some sort of incrementing value?

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1377052
selvakumar.sms
Posted Monday, October 29, 2012 1:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:00 PM
Points: 34, Visits: 202
hi all

still i cant find the solution. please help me out.


this part get the latest seqno from table EPOSSDataExtractSequence, this seqno update every 40 min, once the scheduler run and generate file.

declare @seqno as int

SELECT @seqno = S.ExtractSequenceNo
FROM EPOSSDataExtractSequence S INNER JOIN EPOSSyncOrderMaster M
ON S.MasterCategory = M.MasterCategory
WHERE M.SyncTableName = 'GH_Account_Master_Transfer_Staging'
print @seqno


this part update the latest sequence no from the table EPOSSDataExtractSequence


UPDATE GH_Account_Master_Transfer_Staging
SET SeqNo = @seqno + 1
FROM INSERTED
WHERE
GH_Account_Master_Transfer_Staging.SourceDocNo = INSERTED.SourceDocNo
AND GH_Account_Master_Transfer_Staging.SourceFiscalYear = INSERTED.SourceFiscalYear
AND GH_Account_Master_Transfer_Staging.SourceLocationCode = INSERTED.SourceLocationCode
END


please suggest some idea.

Every experts was once a beginners!!! every pros was once an amateur
Post #1378139
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.