One Trigger in SQL Server 6.5 not working...

Howdy,

Question for the group....

When creating a new database from scripts I encounter a problem.

Everything else works after the database is created except the following trigger. (The code is shown below).
This trigger exists in the database. I verified this through different scripts available from TechNet. I assume if I see the trigger object in the database, it has compiled properly and is enabled. However, it never fires when a record is inserted into the table after the installation has completed. The trigger exists, but does not work at all.

I issued the following command in isql_w (sp_recompile <table_name>). According to the docs this should recompile the trigger the next time the table receives and insert. I tried it from the application.
This did not help either.

Here is a description of the table:
ScheduleId
PatientId
ProviderId
SpecialtyId
LocationId
VisitTypeId
ScheduleDate
ScheduleTime
Duration
Comments
ConfirmAppt
TimeNum
Deleted
Locked
PatientName
VisitType

Identity Seed Increment
------------------------------ ------------ ------------
ScheduleId 1 1

The only solution I have found is to copy the trigger code, paste into isql_w and re-execute this trigger code. From that point on, the trigger works flawlessly. But this install should be automatic.

Can you think of anything I may have missed?

Thanks
PJD


Here is the code I spoke of...

CREATE TRIGGER Schedule_ITrig ON Schedule FOR INSERT AS
DECLARE @TabName varchar(40), @PrimKey int, @TimeAmount smallint, @Unit varchar(20)
DECLARE @pstring char(50),@patid int,@provid int,@schedid int,@fulldate datetime
DECLARE @ordschedid int
SELECT @patid = inserted.patientid,@provid = inserted.providerid,@schedid = inserted.scheduleid
from inserted
Select @ordschedid = -1
While @ordschedid <> null
BEGIN
Select @ordschedid = max(OrderScheduleId)
FROM OrderSchedules
WHERE OrderSchedules.PatientId = @PatId and
OrderSchedules.ProviderId = @ProvId and
OrderSchedules.TableName = `ProvLabOrders` and
OrderSchedules.ScheduleId is Null
If @ordschedid <> null
BEGIN
SELECT @tabname = OrderSchedules.TableName,@Primkey = OrderSchedules.TablePrimKeyId,
@Timeamount = OrderSchedules.ExeRelativeAmount,@unit = TimeUnits.TimeUnit
FROM OrderSchedules, TimeUnits
WHERE OrderSchedules.TimeUnitId = TimeUnits.TimeUnitId and
OrderSchedules.OrderScheduleId = @ordschedid
SELECT @fulldate =convert(datetime,convert(char(10),inserted.schedu ledate,101)+ ` `+ convert(char(11),
inserted.scheduletime,108))
FROM inserted

If @tabname = `ProvLabOrders`
BEGIN
UPDATE ProvLabOrders
SET DateExpected =
CASE
WHEN (@Unit = `second`) then
(select DateAdd(second,@TimeAmount, @fulldate))


WHEN (@Unit = `minute`) then
(select DateAdd(minute,@TimeAmount, @fulldate))

WHEN (@Unit = `hour`) then
(select DateAdd(hour,@TimeAmount, @fulldate))


WHEN (@Unit = `day`) then
(select DateAdd(day,@TimeAmount, @fulldate))


WHEN (@Unit = `week`) then
(select DateAdd(week,@TimeAmount, @fulldate))


WHEN (@Unit = `month`) then
(select DateAdd(month,@TimeAmount, @fulldate))

WHEN (@Unit = `year`) then
(select DateAdd(year,@TimeAmount, @fulldate))
END
WHERE ProvLabOrderId = @PrimKey
UPDATE OrderSchedules
SET ScheduleId = @schedid
WHERE OrderScheduleId = @ordschedid
END
ELSE
If @tabname = `CROrders`
BEGIN
UPDATE CROrders
SET DateExpected =
CASE
WHEN (@Unit = `second`) then
(select DateAdd(second,@TimeAmount, @fulldate))


WHEN (@Unit = `minute`) then
(select DateAdd(minute,@TimeAmount, @fulldate))

WHEN (@Unit = `hour`) then
(select DateAdd(hour,@TimeAmount, @fulldate))


WHEN (@Unit = `day`) then
(select DateAdd(day,@TimeAmount, @fulldate))


WHEN (@Unit = `week`) then
(select DateAdd(week,@TimeAmount, @fulldate))


WHEN (@Unit = `month`) then
(select DateAdd(month,@TimeAmount, @fulldate))

WHEN (@Unit = `year`) then
(select DateAdd(year,@TimeAmount, @fulldate))
END
WHERE CROrderId = @PrimKey
UPDATE OrderSchedules
SET ScheduleId = @schedid
WHERE OrderScheduleId = @ordschedid
END
END
END
GO

Thanks in advance for your time...
PD