disable trigger syntax?

Code Snippet

CREATE PROCEDURE Staff_NoteGroup_insert

(

@.staffID AS int,

@.owner AS int,

@.subject AS varchar(256),

@.message AS varchar(512),

@.date_add AS DateTime

)

AS

BEGIN TRANSACTION SERIALIZABLE

DECLARE @.id as int

SELECT @.id = (SELECT MAX(id) FROM Staff_NoteGroup) + 1

IF @.id IS NULL

SET @.id = 1

INSERT INTO Staff_NoteGroup VALUES(@.id, @.staffID, @.owner, @.subject, GETDATE(), GETDATE())

IF @.@.error <> 0 BEGIN

ROLLBACK

RETURN

END

DISABLE TRIGGER Staff_Note_insert ON DATABASE

INSERT INTO Staff_Note VALUES(1, @.id, @.owner, @.message, @.date_add)

ENABLE TRIGGER Staff_Note_insert ON DATABASE

IF @.@.error <> 0 BEGIN

ROLLBACK

RETURN

END

COMMIT

GO

Below is the error message.

Msg 156, Level 15, State 1, Procedure Staff_NoteGroup_insert, Line 26

Incorrect syntax near the keyword 'TRIGGER'.

Msg 102, Level 15, State 1, Procedure Staff_NoteGroup_insert, Line 28

Incorrect syntax near 'ENABLE'.

Msg 102, Level 15, State 1, Procedure Staff_NoteGroup_insert, Line 33

Incorrect syntax near 'COMMIT'.

Where is the problem?

I'm using sql server 2005

Thanks,

Max

To execute these commands, you will need to put semicolons before disable and enable:

;DISABLE TRIGGER Staff_Note_insert ON DATABASE

INSERT INTO Staff_Note VALUES(1, @.id, @.owner, @.message, @.date_add)

;ENABLE TRIGGER Staff_Note_insert ON DATABASE

This will allow your code to compile. I don't know that this is a good idea, and it seems like you are not using it right (is Staff_Note_insert a database trigger? or is it a trigger on the Staff_Note table? That would be

;DISABLE TRIGGER Staff_Note_insert ON Staff_note)

But why disable it here? There might be a good reason, so I am not saying it is necessarily a bad thing, but at the very least if deserves a comment as to why you are doing this for other readers who might have to maintain this code later

|||

Aah the "ON DATABASE" part is suppose to be "ON Staff_Note", at first I thought I did not use the ON clause correctly, forgot to change it back.

As for the disabling it, The trigger will update Staff_NoteGroup.date_edit column, everytime a new note is added for the same group, and since this procedure is adding a new group which should have at least a single note, the date_edit should be empty.

I will probably remove the trigger later and replace them using procedure, currently adding the note is still using normal SQL, so I need a trigger to update the NoteGroup.

The database is originally an ms access so there is quite a lot of things to change to make use of the server facilities and also to make it safe for multi-user. I still have a lot to learn.

Regards,

Max

|||Just a quick thought on your code, and please keep in mind that this is just my opinion:

I think it may be a better idea to make some sort of check IN the trigger that decides if you want to perform the function the trigger is doing as compared to disabling and re-enabling the trigger every time. I can't say specifically why, but the idea of disabling a trigger on a regular basis makes me uneasy.

Anyway, just my opinion.

dinsdale.
|||

>>I can't say specifically why, but the idea of disabling a trigger on a regular basis makes me uneasy. <<

No doubt!|||

Ah I have not thought about making a check in the trigger.

Why is it bad to disable and enable trigger every time? does it affect performance? data integrity?

This is the first time I use trigger (other than when I'm still a uni student.), so I'm not very experience with the side-effect.

Anyway, that trigger part is now gone, I remove the trigger completely!!, replaced by stored procedure.

My application is adapting to SQL Server quite well.

Regards,

Max