asked on

Optimizing a Trigger

I have a Trigger which runs on Insert on one of my Table. Trigger is as below:-

CREATE TRIGGER [InsertFZE02MAS] ON dbo.ST020100
FOR INSERT
AS
INSERT INTO FZEST02MAS ( Trdate, SC01001, SC01037, SC01038, ISUCode, IPUCode, SL01001,CustomerPO, TRANSACTION_NUMBER)
      SELECT GETDATE(), ST02017, SC010100.SC01037, SC010100.SC01128, SC01135, SC01134, ST02001,OR01072,TRANSACTION_NUMBER
      FROM INSERTED,SC010100,OR010100 WHERE ST02017 = SC010100.SC01001 AND
      ST02009 = OR010100.OR01001

Trigger fails on many occasions, it starts again only when i remove the data from FZEST02MAS table. Can you tell me how can i resolve this?

Guy Hengel [angelIII / a3]

do you have indexes on:
SC010100 ( SC01001 )
ST020100 ( ST02017, ST02009 )
OR010100 ( ST02017 )

imran_fast

hi just to change last index

SC010100 ( SC01001 )
ST020100 ( ST02017, ST02009 )
OR010100 ( OR01001 )

create nonclustered index ix_sc010100 on sc010100(sc01001)

create nonclustered index ix_ST020100 on ST020100( ST02017, ST02009 )

create nonclustered index ix_OR010100 on OR010100 ( OR01001 )

dduser

ASKER

I do have index on SC01001 for SC010100 and OR01001 on OR010100 Tables. If i create Non Clustured Index in ST020100 for ST02017 & ST02009 would that help me?

imran_fast

>> it starts again only when i remove the data from FZEST02MAS table

Please check how much free space you have available for your data(.mdf) file.

chhapia_chintak

Is there any constraintb on FZEST02MAS table which is not there on FZEST02MAS table?
Suppose if SC01001 of FZEST02MAS is a unique key and the trigger try to insert same number then trigger will fail.

dduser

ASKER

Constraints are fine, it is a performance issue not a logical or Syntax error.

imran_fast

>>If i create Non Clustured Index in ST020100 for ST02017 & ST02009 would that help me?
Yes it do.

and did you check the free space. should have few MB free space like 50 to 100.

dduser

ASKER

Imran but remember as shown in Trigger the join clause is utilized only for the Inserted record not for the entire database? Still if we create new index will this help the performance.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial