Trigger not firirng in sql server

Question

Trigger not firirng in sql server

selvakumar.sms

SSC Eights!

Points: 895
More actions
October 25, 2012 at 7:41 am

#270191

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
--------------------------------------------------------
I am Learner -- SQL

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

How are you inserting from a file?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

selvakumar.sms SSC Eights! Points: 895 More actions · Answer 2

no bulk insert , reading line by line and inserting in to the table using .net scheduler.

--------------------------------------------------------

I am Learner -- SQL

DiverKas SSCrazy Points: 2049 More actions · Answer 3

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.

Lowell SSC Guru Points: 323518 More actions · Answer 4

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

selvakumar.sms SSC Eights! Points: 895 More actions · Answer 5

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.

--------------------------------------------------------

I am Learner -- SQL