trigger stopped firing SQL 2000 1

[robincbailey]

A trigger that was working, has for some reason stopped firing.
I know that the trigger has not changed, but what else could be causing this?

CREATE TRIGGER copy_across
ON dbo.rms_inventory_temp
FOR UPDATE, INSERT
AS
DECLARE
@inventid VARCHAR(16),
@description VARCHAR(30),
@location VARCHAR(30),
@commsconnect VARCHAR(25),
@model VARCHAR(10),
@department VARCHAR(8)
SELECT
@inventid=r.inventid,
@description=r.description,
@location=r.location,
@commsconnect=r.commsconnect,
@model=r.model,
@department=r.department
FROM rms_inventory_temp r

IF @inventid=''
BEGIN
Update rms_inventory_temp
set inventid=RMS_badge_numbers.devicename
from RMS_badge_numbers
where rms_inventory_temp.computer_idn=RMS_badge_numbers.computer_idn
END

IF @inventid<>'' and @description<>'' and @location<>'' and @commsconnect<>'' and @model<>'' and @department<>''
BEGIN
insert into rms_inventory_changes
(
prodname,
location,
ip,
inventid,
model,
department
)
select
description,
location,
commsconnect,
inventid,
model,
department
from inserted
where @inventid<>'' and @description<>'' and @location<>'' and @commsconnect<>'' and @model<>'' and @department<>''
END

Delete from rms_inventory_temp where inventid in (select inventid from rms_inventory_changes)

 

[ManjulaMadhu]

you can check on the status of the trigger. It looks as though the trigger is disabled.

 

[Digga]

Hi Robin,

are you doing bulk inserts or individual?

Digga

Sharing Knowledge Saves Valuable Time!

 

[robincbailey]

Thanks Guys,
Firstly how do I tell if a trigger is disabled?
The idea behind this trigger is that single rows get updated and this will copy any row across where all of the fields have been filled

 

[ManjulaMadhu]

you can check in user_objects for status where objecT_name=trigger name and object_type='TRIGGER'

 

[Kapgemini]

Hi manjula.
If u feel that the trigger is disable you can enable it like this

alter table table_name enable trigger trigger_name

 

[ManjulaMadhu]

yes, But the OP was asking how to check the status of the trigger .

 

[tlbroadbent]

Use the ObjectProperty function to determine the status of a Trigger.

If ObjectProperty(Object_ID('copy_across'), 'ExecIsTriggerDisabled') = 1
Print 'Trigger copy_across is disabled'

Robin,

Were you able to resolve the problem? I suspect, the problem wasn't a disabled Trigger but a Trigger that functions differently than expected.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

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

 

[robincbailey]

The trigger is not disabled and is still not firing. This trigger was working for about 8 weeks but has just stopped. I have had this before when one of the other databases had a transaction log that grew to 20GB, I have since deleted the log file and set a limit on against it of 2GB. I am wondering if the log file is to big again yet I have 30GB space free. I reallt an stuck with this one and am finding myself looking at the trigger code which is silly really as it was working.

 

[SQLBill]

Have any table columns been renamed?

-SQLBill

 

[tlbroadbent]

How do you know the trigger is not firing? How many rows does the rms_inventory_temp table contain? If the table contains rows, how many have @inventid<>''?

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

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