Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trigger not firirng in sql server Expand / Collapse
Author
Message
Posted Thursday, October 25, 2012 7:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 3:15 AM
Points: 54, Visits: 446
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
Post #1376990
Posted Thursday, October 25, 2012 8:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
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
Posted Thursday, October 25, 2012 8:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 3:15 AM
Points: 54, Visits: 446
no bulk insert , reading line by line and inserting in to the table using .net scheduler.

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

I am Learner -- SQL
Post #1377039
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: Friday, August 16, 2013 8:28 AM
Points: 249, Visits: 460
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
Posted Thursday, October 25, 2012 8:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 12,910, Visits: 32,026
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
Posted Monday, October 29, 2012 1:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 3:15 AM
Points: 54, Visits: 446
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
Post #1378139
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse