Automatic trigger disabling - please help

Question

Post reply

Automatic trigger disabling - please help

Rohitav Samanta

SSC Enthusiast

Points: 135
More actions
July 27, 2006 at 1:21 pm

#62565

I have a trigger that fires on insert of a new row in our jobseekerservices table.
Something happened and the trigger was autmatically disabled by SQL server.
The alter statement is at the end of the trigger.
I want to know uder what conditions does SQL server disable triggers and how to prevent it?
----------------------------------------------------------------
CREATE TRIGGER Insert_JobSeekerDevelopmentPlans
ON JobSeekerServices
FOR INSERT
AS
SET NOCOUNT ON
IF (SELECT COUNT(*) FROM Inserted
   WHERE SERVICEUID = '{9CA51258-C892-4C2B-B67C-EB20D6F8C2D2}') = 0   -- WP900
BEGIN
  RETURN
END

IF NOT EXISTS (Select * From JobSeekerDevelopmentPlans
  WHERE PlanTypeCD = 'REEMPLOYMENT' and JobSeekerUID = (Select jobseekeruid from inserted))

BEGIN

  INSERT INTO JobSeekerDevelopmentPlans
  (PlanUID,
  JobSeekerUID,
  Title,
  PlanTypeCD,
  StartDate,
  StatusCD,
  OutcomeCD,
  CreatedByUserUID,
  CreatedDate )
  SELECT newid(),JobSeekerUID,'ReEmployment (UIP) Plan','REEMPLOYMENT',ActualStartDate,
  'OPEN','IN_PROGRESS','{344DAC58-B4F2-4C54-9C27-54C356D73F2D}',GetDate()
  from Inserted

END

alter table [dbo].[JobSeekerServices] disable trigger [Insert_JobSeekerDevelopmentPlans]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mngong SSCertifiable Points: 6167 More actions · Answer 1

You create a trigger and disable it right away .

Should ,nt it be disabled?

Rohitav Samanta SSC Enthusiast Points: 135 More actions · Answer 2

The disabling is being done by SQL Server automatically.

The last bit of code where the trigger is getting disabled is being generated by SQL server automatically.

Bhushan Default port Points: 1427 More actions · Answer 3

remove the following line from source code and compile the trigger

alter table [dbo].[JobSeekerServices] disable trigger [Insert_JobSeekerDevelopmentPlans]

Rohitav Samanta SSC Enthusiast Points: 135 More actions · Answer 4

Removing the following line

alter table [dbo].[JobSeekerServices] disable trigger [Insert_JobSeekerDevelopmentPlans]

and recompiling it will work.

But my question is what causes this and how to prevent it in the future?

Lance Sanders SSC Eights! Points: 903 More actions · Answer 5

SQL Server will not alter your source code for you. I suspect a faulty script is to blame. There needs to be a GO between the END and the ALTER TABLE.