selvakumar.sms (10/25/2012)
hi allI 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.