ERROR on create trigger statement

• allan audi

  Right there with Babe

  Points: 738

  More actions

  March 28, 2006 at 7:36 am

  #171024

  Whenever i run the following code error message below appears. does anybody know how to go about it?

  create trigger tr_delete_ on testscreen

  after delete

  as

  begin

  insert del_screen

  select *,getdate()

  from deleted

  end

   

  ERROR MESSAGE:

  An explicit value for the identity column in table 'del_screen' can only be specified when a column list is used and IDENTITY_INSERT is ON.

  Thanks,

  Allan.

• AMIT GUPTA-263376

  SSCarpal Tunnel

  Points: 4244

  More actions

  March 28, 2006 at 7:47 am

  #629168

  Hi,

  Your table 'del_screen'  contain identity column.

  To insert all the values you must specify all the column names of a table. and

  set IDENTITY_INSERT on

  Thanks.

  Regards,

  Amit Gupta

   

• sushila

  SSC-Dedicated

  Points: 35293

  More actions

  March 28, 2006 at 9:25 am

  #629221

  Like so...

  SET IDENTITY_INSERT testscreen ON

  INSERT INTO del_screen(col1, col2, col3, date....)

  SELECT col1, col2, col3...getdate() FROM deleted

  SET IDENTITY_INSERT testscreen OFF

  ..however, it seems that all you really want to do is insert your deleted rows in the "del_screen" table - there're several ways of doing this...

  1) You could set your "deletedDate" column in the testscreen table with a default getDate()

  2) You could add an additional column to your existing table "testscreen" that could just be a "logical" delete.

  3) You don't need to make your "del_screen" column into an Identity one since you're inserting IDs from your "testscreen" table...

  
  
  
  
  
  
  **ASCII stupid question, get a stupid ANSI !!!**

• allan audi

  Right there with Babe

  Points: 738

  More actions

  March 29, 2006 at 3:41 am

  #629421

  this is what i used and it work so well...

  create trigger tr_delete_testscreen

  on testscreen

  after delete

  as

  begin

  insert del_screen

  (RecID, InterviewPK, InterviewerCode, InterviewDate, DSSID, HouseholdID, AgeYrMo, AgeDays, SelfProxy, ProxyType, ProxyOther, ProxyReason,

                        OtherReason, DeathDate, CannotFill, RespSymp, GastSymp, YellowEyes, Fever, Otherillness, OtherillDesc, SameIllness, SeekCare, LwakCH, OtherCH,

                        CHovernight, CHnightNum, timestamps, filenum)

  select RecID, InterviewPK, InterviewerCode, InterviewDate, DSSID, HouseholdID, AgeYrMo, AgeDays, SelfProxy, ProxyType, ProxyOther, ProxyReason,

                        OtherReason, DeathDate, CannotFill, RespSymp, GastSymp, YellowEyes, Fever, Otherillness, OtherillDesc, SameIllness, SeekCare, LwakCH, OtherCH,

                     CHovernight, CHnightNum, timestamps, filenum

  from deleted

  end

  go

   

   

  thanks for the help