Problem with trigger
-
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
-
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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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
-
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply