Problem with SQL 7.0 adding Trigger

[LLudden]

I am doing some work at a new customer who is running SQL 7 (unsure of Service pack #). I need to add an update trigger to one of the tables which contains about 70K records. There are currently no triggers defined on that (or any) tables in the database.

When I enter the T-SQL for the trigger and hit apply, I never get control back (it ran for 14hrs and still didn't finish). I got the same result using a backup of the database which had no current users.

Any idea what's happening and how to fix it? If I add that same trigger to an empty table, it works fine.

 

[JamesLean]

Can you post the code you are running to create the trigger. That may give us more of an idea of the problem.

 

[LLudden]

CREATE TRIGGER [MakeorDeleteStops] ON [dbo].[Transport]
FOR UPDATE
AS
Declare @Ticket int, @Transport int
if ( UPDATE([Vehicle ID]) )
begin
Select @Ticket = (Select [Ticket No] From Inserted)
Select @Transport = (Select [Vehicle ID] from Inserted)
if ( @Transport > 0 )
begin
exec create_DOFromTicket @Ticket, @Transport, 1
exec create_PUFromTicket @Ticket, @Transport, 1
end
Else
exec delete_TicketFromStopTable @Ticket
end
GO

 

[JamesLean]

I can't see an immediate problem with your code. I take it you are trying to use EM to create the trigger (clicking apply)?

Have you tried running that code from QA? Did you get an error?

 

[tlbroadbent]

Creating a TRIGGER doesn't update the rows in the table so it doesn't matter how many rows the table contains. If the CREATE TRIGGER statment hangs it is likely due to blocking. Are you sure there are no other users? If there are no other users, could you have another connection that is blocking? Perhaps a system process has locked the table or schema.

Use Enterprise Manager or one of the following system stored procedures to see if there is any blocking on the server.

sp_lock, sp_who2 Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[LLudden]

I have tried adding this script both from QA and from EM. Both went off into nevernever land, and after 14hrs we rebooted the server to speed things up, so it was doing something.

There has been no tech people working on this system for a long time. The databases and tables were all created by the owner of the company who had no prior SQL or database experience and little if any formal training. If you can think of anything that might cause this problem, reply here please.

Thanks