Insert,Update and delete triggers in sql server 2005

• Aditya-313343

  Right there with Babe

  Points: 768

  More actions

  June 20, 2010 at 11:28 pm

  #140948

  **********************Delete Trigger************************************/

  CREATE TRIGGER [dbo].[LogeProdChangesDEL] ON [dbo].[eProd] AFTER DELETE

  AS

  INSERT INTO [eProdNewTriggerLog]

  SELECT Magic.[Col1],'',

  Magic.[Col2],'',

  Magic.[Col3],'',

  Magic.[Col4],'',

  Magic.[Col5],'',

  Magic.[Col6],'',

  Magic.[Col7],'',

  getdate(),system_user,'Delete'

  FROM Deleted Magic

  GO

  ------------------Insert trigger--------------------------------

  CREATE TRIGGER [dbo].[LogeProdChangesINS] ON [dbo].[eProd] AFTER INSERT

  AS

  INSERT INTO [eProdNewTriggerLog]

  SELECT '',Magic.[Col1],

  '',Magic.[Col2],

  '',Magic.[Col3],

  '',Magic.[Col4],

  '',Magic.[Col5],

  '',Magic.[Col6],

  '',Magic.[Col7],

  getdate(),system_user,'Insert'

  FROM Inserted Magic

  ********************************Update Trigger***********************************/

  CREATE TRIGGER [dbo].[LogeProdChangesUPD] ON [dbo].[eProd] AFTER UPDATE

  AS

  INSERT INTO [eProductionNewTriggerLog]

  SELECT Magic.[Col1],eP.[Col1],

  Magic.[Col2],eP.[Col2],

  Magic.[Col3],eP.[Col3],

  Magic.[Col4],eP.[Col4],

  Magic.[Col5],eP.[Col5],

  Magic.[Col6],eP.[Col6],

  Magic.[Col7],eProd.[Col7],

  getdate(),ep.ModifiedBy,'Update'

  FROM Inserted Magic, Deleted eP

  I have created those 3 triggers on a table to get fired whenever data is inserted or update or deleted.so,actually when some thing is changed in the table these triggers should fire accordingly and insert data into log table.But,during updation or insertion these triggers are inserting 1000's of rows for each single manipulation.

  ex:i have updated around 13 rows and it nearly inserted about 169 rows into log table.Which is actullay square of that number

  Could any one please guide me whether i have created anything wrong in those triggers.

  Thanks.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 21, 2010 at 2:09 am

  #1182568

  In your update trigger you have a cross join between the inserted and deleted tables. You need to join them properly.

  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