Problem with trigger

• Robbs

  Old Hand

  Points: 346

  More actions

  November 16, 2004 at 2:52 pm

  #165451

  I have defined a trigger on a table to basically select some of the columns and put them in another table upon insert or update. The trigger checks to see if the table exists and drops it if it does before the select into occurs.

  My problem is that it does not work. The syntax check is okay so I do not understand what is wrong?

  Thanks for any help.

  My trigger code is as follows

  CREATE TRIGGER [put_pima] ON [dbo].[ASSAY_SAMPLE]

  FOR  INSERT, UPDATE

  AS

  BEGIN

  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UDEF_PIMA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

  drop table [dbo].[UDEF_PIMA] ;

  SELECT SAMPLE_NUMBER, ID_NUMBER, depth_1, depth_2, TYPE_CODE, Comments

  INTO UDEF_PIMA

  FROM  ASSAY_SAMPLE

  WHERE (Extension IS NOT NULL);

  END

• Phil Parkin

  SSC Guru

  Points: 246986

  More actions

  November 16, 2004 at 4:11 pm

  #530380

  As you are not relying on the 'inserted' table in any way, is there any reason why you don't just create a view over the dbo.assay_sample table?

  create view vwUDEF_PIMA as

  SELECT SAMPLE_NUMBER, ID_NUMBER, depth_1, depth_2, TYPE_CODE, Comments

  FROM ASSAY_SAMPLE

  WHERE (Extension IS NOT NULL)

  Then you can delete the trigger and the problem goes away

  Phil

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Johan Bijnens

  SSC Guru

  Points: 135376

  More actions

  November 17, 2004 at 2:50 am

  #530439

  Keep in mind a trigger fires every time the criteria meets insert or update (in your case) !

  How many times do you predict this to happen and measure afterward ?

  What you wish to do is drop and create a table !?? meaning catalog-locks,..

  Triggers are meanth to be short and quick in-transaction operations.

  What if someone is running a select on the table to be dropped/recreated ?

  Then your transaction will fail because the object cannot be dropped, so your insert or update will fail !

  IMO what you are desinging here is selfdestructive

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• Robbs

  Old Hand

  Points: 346

  More actions

  November 17, 2004 at 7:25 am

  #530476

  Thanks for the advice. I see that a view is what I should do and the best way to get what I need in this case.