Insert Record based on Insert Trigger

• Jason Willis

  SSC Veteran

  Points: 225

  More actions

  August 10, 2004 at 7:12 am

  #88373

  I want to insert 2 records almost identical to the first if the record being inserted has a particular value in a field. i.e Day Task

  Pseudo Code

  INSERT INTO HlpDsk (NAMEL, NAMEF, Priority, Office, Problem_Category, Sub_Category, SPA, Tech_Queue, Assigned_By, RING, EmailFLG, Date_Recieved)

  VALUES ("Willis", "Jason", "Normal", "PEN", "Task", "Change Backup", "Jason.Willis", "Jason.Willis", "V.Holloman", "60A", "Y", getdate())

  Where Problem_Category = 'Day Task'

  I hope I explained what I am trying to do well enough, I am new to this.

   

   

• rcavill

  Default port

  Points: 1482

  More actions

  August 11, 2004 at 5:36 am

  #518450

  Using  derived table [AddTaskList] gives all the additional tasks to be also inserted, and then only inserted

  when the Problem_Category is of the given value.

  /* code */

  create trigger tr_HlpDsk_AdditionalTask on HlpDsk

  for insert

  as

  INSERT INTO HlpDsk (NAMEL, NAMEF, Priority, Office,

   Problem_Category,

  Sub_Category, SPA, Tech_Queue, Assigned_By,

  RING, EmailFLG, Date_Recieved)

  select NAMEL, NAMEF, Priority, Office,

  AdditionalTask, /* use different task list */

  Sub_Category, SPA, Tech_Queue, Assigned_By,

  RING, EmailFLG, Date_Recieved

  from inserted i

  full join

  (

  select convert(varchar(10),'') as AdditionalTask  where 1 = 0

  union all Select 'Day Task 2'

  union all Select 'Day Task 3'

  ) AddtaskList

  where i.Problem_Category = 'Day Task'

  GO

  /* end code */