Rollback Not working correctly in Trigger on MSP_TASKS

[Deepak Mahadevan]

I have an Update Trigger on MSP_TASKS. (Project 2003).
The update trigger basically updates an external database
(non-Project). If it encounters a problem, a ROLLBACK is issued. But
when I look at the data in Project after rollback, some data is still
being committed. This problem is mainly with a custom Flag field at
Task level.
Lets say the flag can have two values either a 1 or 0, the business
rule says that user should not be allowed to change a flag value from a
1 to 0. The trigger is able to detect this change and raises error and
issues a ROLLBACK. But the flag value doesnt get rolled back.
Does any one know why this could happen? I am willing to share more
details if needed.

Thanks
PS

 

[Lars Hammarberg]

First, I'd propose having this kind of interference with what the user does
and what actually happens directly in the client application - wouldn't it
be better having this check inside Project, allowing for a dialogue box
telling the user this change will be rolled back?

That said - when you make changes in the database you'll have to set the
corresponding EXT_EDIT flags too - check the documentation htm files or get
back to us in microsoft.public.project.developer and we'll take it from
there.

 

[Brian K - Project MVP]

I have an Update Trigger on MSP_TASKS. (Project 2003).
The update trigger basically updates an external database
(non-Project). If it encounters a problem, a ROLLBACK is issued. But
when I look at the data in Project after rollback, some data is still
being committed. This problem is mainly with a custom Flag field at
Task level.
Lets say the flag can have two values either a 1 or 0, the business
rule says that user should not be allowed to change a flag value from a
1 to 0. The trigger is able to detect this change and raises error and
issues a ROLLBACK. But the flag value doesnt get rolled back.
Does any one know why this could happen? I am willing to share more
details if needed.

Thanks
PS

Yikes!!! It is really a bad idea to have this kind of thing going on
against Project's tables. you are pretty lucky that it did not really blow
up your project.

You would be best served enforcing these rules within an application level
VBA event.

http://techrepublic.com.com/5100-10878_11-5032122.html#
This shows how to use these events. The example here looks at changes and
asks for a password but you can change the code to your own needs.

 

[Josh C]

I'm having a similar problem with an UPDATE trigger on MSP_FLAG_FIELDS
table.

I have a custom flag and I do not want to allow the user to change the
value from yes to no. If they do, I want to roll back the change, and
display a message.

The rollback works, however for some reason the raiserror does not
cause a message to be displayed in Project 2003.

Any ideas why?

declare @foovaluenew int
declare @foovalueold int

select @foovaluenew = flag_value from inserted
select @foovalueold = flag_value from deleted

if @foovaluenew is null and @foovalueold = 1
begin
rollback transaction
raiserror('cannot change flag to no.', 16, 1)
return
end

return

 

[Brian K - Project MVP]

I'm having a similar problem with an UPDATE trigger on MSP_FLAG_FIELDS
table.

I have a custom flag and I do not want to allow the user to change the
value from yes to no. If they do, I want to roll back the change, and
display a message.

The rollback works, however for some reason the raiserror does not
cause a message to be displayed in Project 2003.

Any ideas why?

declare @foovaluenew int
declare @foovalueold int

select @foovaluenew = flag_value from inserted
select @foovalueold = flag_value from deleted

if @foovaluenew is null and @foovalueold = 1
begin
rollback transaction
raiserror('cannot change flag to no.', 16, 1)
return
end

return

No idea at all. But the same goes for you as for what i said to Mr.
Mahadevan. Do not do update triggers on Project tables. Use VBA.

 

[Josh C]

If the user turns their macro security settings to high, thus disabling
macros, the logic won't be implemented, correct?

That was the reason I was using a trigger, to prevent the user from
disabling the check by turning off macros.

 

[Brian K - Project MVP]

If the user turns their macro security settings to high, thus disabling
macros, the logic won't be implemented, correct?

That was the reason I was using a trigger, to prevent the user from
disabling the check by turning off macros.

Users can do lots of things. you could have a network management job go
through and set everybodies macro settings and then disable their ability
to change them.