Trigger with merge statement

• olibbhq

  SSCarpal Tunnel

  Points: 4034

  More actions

  July 6, 2010 at 7:35 am

  #237303

  Hi,

  Playing around with other options on triggers. Currently have this

  ALTER TRIGGER

  [dbo].[POP_HEADER_LOGS] ON [dbo].[t_curve]

  AFTER INSERT, UPDATE

  AS

  BEGIN

  SET NOCOUNT ON;

  Merge

  Into TEAM.dbo.HEADER_ME_T_CURVE As Tgt

  Using INSERTED As Src

  On

  Src.PK_INDEX = Tgt.PK_INDEX

  When Matched

  Then

  Update Set

  TGT.pk_index=SRC.pk_index,

  TGT.f_id=SRC.f_id,

  TGT.f_curvesetid=SRC.f_curvesetid,

  TGT.f_wellid=SRC.f_wellid,

  TGT.f_curvetypeid=SRC.f_curvetypeid,

  TGT.f_run=SRC.f_run,

  TGT.f_version=SRC.f_version,

  TGT.f_description=SRC.f_description,

  TGT.f_index=SRC.f_index,

  TGT.f_curveplotstyleid=SRC.f_curveplotstyleid,

  TGT.f_usedefaultplotstyle=SRC.f_usedefaultplotstyle,

  TGT.f_topdepth=SRC.f_topdepth,

  TGT.f_basedepth=SRC.f_basedepth,

  TGT.f_maxvalue=SRC.f_maxvalue,

  TGT.f_minvalue=SRC.f_minvalue,

  TGT.f_source=SRC.f_source,

  TGT.f_notes=SRC.f_notes,

  TGT.f_released=SRC.f_released,

  TGT.f_rundate=SRC.f_rundate,

  TGT.f_final=SRC.f_final,

  TGT.f_created=SRC.f_created,

  TGT.f_creator_id=SRC.f_creator_id,

  TGT.f_modified=SRC.f_modified,

  TGT.f_modifier_id=SRC.f_modifier_id

  When Not Matched

  Then

  Insert

  (

  pk_index,

  f_id,

  f_curvesetid,

  f_wellid,

  f_curvetypeid,

  f_run,

  f_version,

  f_description,

  f_index,

  f_curveplotstyleid,

  f_usedefaultplotstyle,

  f_topdepth,

  f_basedepth,

  f_maxvalue,

  f_minvalue,

  f_source,

  f_notes,

  f_released,

  f_rundate,

  f_final,

  f_created,

  f_creator_id,

  f_modified,

  f_modifier_id

  )

  Values

  (

  SRC.pk_index,

  SRC.f_id,

  SRC.f_curvesetid,

  SRC.f_wellid,

  SRC.f_curvetypeid,

  SRC.f_run,

  SRC.f_version,

  SRC.f_description,

  SRC.f_index,

  SRC.f_curveplotstyleid,

  SRC.f_usedefaultplotstyle,

  SRC.f_topdepth,

  SRC.f_basedepth,

  SRC.f_maxvalue,

  SRC.f_minvalue,

  SRC.f_source,

  SRC.f_notes,

  SRC.f_released,

  SRC.f_rundate,

  SRC.f_final,

  SRC.f_created,

  SRC.f_creator_id,

  SRC.f_modified,

  SRC.f_modifier_id

  )

  ;

  END

  For the delete part I want to add something like

  WHEN NOT MATCHED BY SOURCE THEN delete where tgt.PK_INDEX = deleted.PK_INDEX

  but this doesnt work

  error is

  Msg 156, Level 15, State 1, Procedure POP_HEADER_LOGS, Line 109

  Incorrect syntax near the keyword 'where'.

  Is this because I cant reference the temporary 'deleted' table in this statement.

  Thanks for the help, you have saved me many times.

  Cheers,

  Oliver

• olibbhq

  SSCarpal Tunnel

  Points: 4034

  More actions

  July 6, 2010 at 7:47 am

  #1189102

  Also I have tried with this slight code change.

  But get error:

  Msg 5334, Level 16, State 2, Procedure POP_HEADER_LOGS, Line 108

  The identifier 'deleted.PK_INDEX' cannot be bound. Only target columns are allowed in the 'WHEN NOT MATCHED BY SOURCE' clause of a MERGE statement.

  USE [ODM_TEST_ME]

  GO

  /****** Object: Trigger [dbo].[POP_HEADER_LOGS] Script Date: 07/06/2010 13:51:34 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  -- =============================================

  -- Author:<Oliver>

  -- Create date: <20100705>

  -- Description:<ODM Log works>

  -- =============================================

  ALTER TRIGGER

  [dbo].[POP_HEADER_LOGS] ON [dbo].[t_curve]

  AFTER INSERT, UPDATE, DELETE

  AS

  BEGIN

  SET NOCOUNT ON;

  Merge

  Into TEAM.dbo.HEADER_ME_T_CURVE As Tgt

  Using INSERTED As Src

  On

  Src.PK_INDEX = Tgt.PK_INDEX

  When Matched

  Then

  Update Set

  TGT.pk_index=SRC.pk_index,

  TGT.f_id=SRC.f_id,

  TGT.f_curvesetid=SRC.f_curvesetid,

  TGT.f_wellid=SRC.f_wellid,

  TGT.f_curvetypeid=SRC.f_curvetypeid,

  TGT.f_run=SRC.f_run,

  TGT.f_version=SRC.f_version,

  TGT.f_description=SRC.f_description,

  TGT.f_index=SRC.f_index,

  TGT.f_curveplotstyleid=SRC.f_curveplotstyleid,

  TGT.f_usedefaultplotstyle=SRC.f_usedefaultplotstyle,

  TGT.f_topdepth=SRC.f_topdepth,

  TGT.f_basedepth=SRC.f_basedepth,

  TGT.f_maxvalue=SRC.f_maxvalue,

  TGT.f_minvalue=SRC.f_minvalue,

  TGT.f_source=SRC.f_source,

  TGT.f_notes=SRC.f_notes,

  TGT.f_released=SRC.f_released,

  TGT.f_rundate=SRC.f_rundate,

  TGT.f_final=SRC.f_final,

  TGT.f_created=SRC.f_created,

  TGT.f_creator_id=SRC.f_creator_id,

  TGT.f_modified=SRC.f_modified,

  TGT.f_modifier_id=SRC.f_modifier_id

  When Not Matched

  Then

  Insert

  (

  pk_index,

  f_id,

  f_curvesetid,

  f_wellid,

  f_curvetypeid,

  f_run,

  f_version,

  f_description,

  f_index,

  f_curveplotstyleid,

  f_usedefaultplotstyle,

  f_topdepth,

  f_basedepth,

  f_maxvalue,

  f_minvalue,

  f_source,

  f_notes,

  f_released,

  f_rundate,

  f_final,

  f_created,

  f_creator_id,

  f_modified,

  f_modifier_id

  )

  Values

  (

  SRC.pk_index,

  SRC.f_id,

  SRC.f_curvesetid,

  SRC.f_wellid,

  SRC.f_curvetypeid,

  SRC.f_run,

  SRC.f_version,

  SRC.f_description,

  SRC.f_index,

  SRC.f_curveplotstyleid,

  SRC.f_usedefaultplotstyle,

  SRC.f_topdepth,

  SRC.f_basedepth,

  SRC.f_maxvalue,

  SRC.f_minvalue,

  SRC.f_source,

  SRC.f_notes,

  SRC.f_released,

  SRC.f_rundate,

  SRC.f_final,

  SRC.f_created,

  SRC.f_creator_id,

  SRC.f_modified,

  SRC.f_modifier_id

  )

  WHEN NOT MATCHED BY SOURCE and tgt.PK_INDEX = deleted.PK_INDEX

  THEN

  delete

  ;

  END

• July 6, 2010 at 8:26 am

  This was removed by the editor as SPAM

• olibbhq

  SSCarpal Tunnel

  Points: 4034

  More actions

  July 6, 2010 at 8:35 am

  #1189124

  Hi thanks for the reply,

  yes this does work, the only issue with this is that I only want it to delete records that I have deleted in the table, not all the records that don't match with the inserted table.

  so i only would like the record that matches the deleted record (from the deleted table) to be removed.

  Thanks for your help with this, I am close to getting it but frustratingly not close enough.

  Oliver

• July 6, 2010 at 11:54 pm

  This was removed by the editor as SPAM

• olibbhq

  SSCarpal Tunnel

  Points: 4034

  More actions

  July 7, 2010 at 7:44 am

  #1189614

  Thank you very much for taking the time to work on this I really appreciate it.

  I added you code and when I try to run it I get this error

  Msg 207, Level 16, State 1, Procedure POP_HEADER_LOGS, Line 46

  Invalid column name 'PK_INDEX'.

  It still looks like there is an issue in the trigger trying to query a value outside the merge statement.

  If you have any ideas please let me know.

  Many Thanks, I really appreciate your help,

  Oliver

• July 7, 2010 at 11:22 pm

  This was removed by the editor as SPAM

• olibbhq

  SSCarpal Tunnel

  Points: 4034

  More actions

  July 8, 2010 at 5:18 am

  #1190140

  Hi thank you for your continued help with this.

  I added the trigger and got an error trying to run it, it still looks like I cant reference a vairable outside the merge statement for some reason.

  Msg 5334, Level 16, State 1, Procedure POP_HEADER_LOGS, Line 137

  The identifier 'Deleted_Index' cannot be bound. Only target columns are allowed in the 'WHEN NOT MATCHED BY SOURCE' clause of a MERGE statement.

  Thank you for your help, I have written a seperate delete tigger. I was looking to have a single trigger using merge to do all the changes but its fine.

  Thanks,

  Oliver

• mpowrie

  SSC Rookie

  Points: 39

  More actions

  July 7, 2015 at 8:54 pm

  #1810837

  A few years late with the reply, but as this came up in a Google search, try this:

  WHEN NOT MATCHED BY SOURCE AND tgt.PK_INDEX = (SELECT PK_INDEX from Deleted)