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

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

disable containter with runtime value does not work

Il try to disable a containter by an expression but it seems not to work (in VS Debug Mode).

I have an variable defined with int value 1

I use the expression !(@.[User::InteresentenAnzahl]>0) to disable the container.

In between i use and select count(*) from table to populate the variable with the record count.

MY problem is - ssis uses always the design time value of the variable and not the runtime value from the variable.

What could be wrong? Is this possible?

I want to disable tasks because my dependency chain require that some later (dependend) tasks need to be executed.

Best Regards

HANNES

Why you want to enable/disable task based on a condition? Perhpars, you need to make a better use of the precedence constraints in the control flow (yes, the green arrows). You can use expression there that would evaluate the value of the variable and decide wheter or not execute task|||

i have a chain of lets say three containters - container one queries the records, countainer two has something to do or not dependend on result of container one (variable) - and countainer tree does something special what need to be done after two.

If I use an expression all later containers does not execute - but if i disable container two contaier three does execute - and thats what i need.

Could you tell me how to configure precedence constraints if the prev container does not execute and the following should execute.

(

this explanation is shorted for the forum - in reall it is processing with some more dependent tasks

1) count new records

2) process add if new records and

3) set last prcessing date (for reporting)

- and processing add needs 20 minutes for zero rows - so why should i execute it) - but I need the last potential processed date to show up how actual data is - this needs to run after a sucessfull process oder if the package runs and no process occured

)

Best regards

hannes

|||

hmayer wrote:

i have a chain of lets say three containters - container one queries the records, countainer two has something to do or not dependend on result of container one (variable) - and countainer tree does something special what need to be done after two.

If I use an expression all later containers does not execute - but if i disable container two contaier three does execute - and thats what i need.

Could you tell me how to configure precedence constraints if the prev container does not execute and the following should execute.

Take a look at this blog post: http://bi-polar23.blogspot.com/2007/05/conditional-task-execution.html

It's not 100% what you're trying to accomplish, but I believe the same technique that worked for me should work for you in this scenario as well.

Basically what you need is an extra container around that thing (be it a task or a container) that you want to disable. Then in the precedence constraint from that new outer container, have the constraint based on an expression. The outer container will execute regardless of whether the inner one does, so the precidence constraints that flow from it will be valid.

And if you need a way to tie a task's execution to an expression but do not know if the task above it will execute, you can add a placeholder Script task above it.

Both of these techniques are demonstrated in this blog post, but please post back here if this doesn't give you all that you need.

|||You should connect the first container to the second and third containers. Also connect the second to the third. Then set the precedence constraints between 1 & 2 with the constraint being between 1 & 3 the converse of 1 & 2.

So, for example:

constraint between 1 & 2 : Execute 2 if myVar == 1
constraint between 1 & 3: Execute 3 if myVar != 1|||

Phil Brammer wrote:

constraint between 1 & 2 : Execute 2 if myVar == 1
constraint between 1 & 3: Execute 3 if myVar != 1

I am wrong? but if for container three containter two and container one is required then 3 does not execute if 2 does not execute because 2 &3 does never report completion!

And if I do not have a constraint between 2 & 3 - 3 gets executed if 2 fails!

|||

hmayer wrote:

Phil Brammer wrote:

constraint between 1 & 2 : Execute 2 if myVar == 1
constraint between 1 & 3: Execute 3 if myVar != 1

I am wrong? but if for container three containter two and container one is required then 3 does not execute if 2 does not execute because 2 &3 does never report completion!

And if I do not have a constraint between 2 & 3 - 3 gets executed if 2 fails!

I do not believe that you are wrong - this is the reason I use "empty" Script tasks as placeholders in this sort of scenario so that there is some task from which a precedence constraint can be originated without adding any "real" functionality to the package.

|||

hmayer wrote:

I am wrong? but if for container three containter two and container one is required then 3 does not execute if 2 does not execute because 2 &3 does never report completion!

And if I do not have a constraint between 2 & 3 - 3 gets executed if 2 fails!

First, you set up an OR condition, not an AND condition on the precedence constraints on #3. Also, you do have a precedence constraint between 2 and 3. There just isn't an expression there. (You can put one there if needed.)

#1 -- myVar==1 --> #2 -> #3
| myVar!=1 --> #3|||

Im sorry, but I did not get it.

IF I Setup OR then

- #3 maybe executed befor two

- and whats more dramatic #3 maybe executed if #2 fails - and thats not allowed

|||

Phil,

I think that approach does not cover the fact 3 should not be executed if 2 fails.

A while ago, In this post I used a similar approach to what Matt describes; in my case I use an empty dataflow as a place holder, so Script task is not the only one you can use.

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

|||

hmayer wrote:

Im sorry, but I did not get it.

IF I Setup OR then

- #3 maybe executed befor two

- and whats more dramatic #3 maybe executed if #2 fails - and thats not allowed

No. Not true.

SC1 will flow to either SC2 or SC3 depending on the expression and how its evaluated. If the flow goes down the SC2 path, then it will execute SC3 only if SC2 was successful. In this case, the execution of SC1 is irrelevant because of the OR'd precedence constraint. (The expression between SC1 and SC2 would be false in this case).

If the flow goes down the SC3 path because @.myVar != 1, then SC2 will never get executed.

Does this make sense?|||

Hi Refael - thats why i ask.

In addition - why does my original aproach - disable - not work, this sould be fine for all my requirements.

(is it a bug - then i will report it - if it works as design I cannot understand)

Best Regards

HANNES

|||

hmayer wrote:

In addition - why does my original aproach - disable - not work, this sould be fine for all my requirements.

(is it a bug - then i will report it - if it works as design I cannot understand)

I don't know if this is a bug or an underdocumented feature, but it appears that you are not the only one with this issue:

http://www.developerdotstar.com/community/node/345

I've always thought of the Disable property as a design-time tool, not a run-time tool, although I've never seen it officially documented as such.

|||

Rafael Salas wrote:

Phil,

I think that approach does not cover the fact 3 should not be executed if 2 fails.

Are you sure?|||

Phil Brammer wrote:

Rafael Salas wrote:

Phil,

I think that approach does not cover the fact 3 should not be executed if 2 fails.

Are you sure?

Pretty sure...

In this control flow, it looks to me like if Sequence Container 1 succeeds and Sequence Container 2 fails, Sequence Container 3 will still execute. I may be missing something, but I remember fighting with similar approaches when coming up with my "Script task as placeholder) technique.

Can you (you, who have the sample package from which the image was taken ) test it quickly?