Rollback trigger

31 Aug 2006 4:25 PM

jenks

I am trying to create a trigger that rolls back updates to a specific columns
in a table under certain conditions. The area I am having trouble with is
only rolling back updates to these columns and allowing updates to other
columns in the table.

The table is very simple

Company Contact Address1 Address2 City State Zip Phone1 Phone2 Phone3 Fax

If the updating user is <> 'dnccuser' and Phone1, Phone2, Phone3, Fax begin
with 'DNC', I want updates to Phone1, Phone2, Phone3, Fax to roll back but
all other columns can be updated.

Any help is very much appreciated.

31 Aug 2006 4:43 PM

Ryan

Then what you want is not really a rollback, but a selective update.

Write your trigger to re-update the table updating all columns, except
Phone1, Phone2, Phone3, Fax to be taken from the inserted table, and the
columns for Phone1, Phone2, Phone3, Fax to be taken from the deleted table.

Ryan
www.ddrs.net

Show quote

"jenks" wrote:

> I am trying to create a trigger that rolls back updates to a specific columns
> in a table under certain conditions. The area I am having trouble with is
> only rolling back updates to these columns and allowing updates to other
> columns in the table.
>
> The table is very simple
>
> Company Contact Address1 Address2 City State Zip Phone1 Phone2 Phone3 Fax
>
> If the updating user is <> 'dnccuser' and Phone1, Phone2, Phone3, Fax begin
> with 'DNC', I want updates to Phone1, Phone2, Phone3, Fax to roll back but
> all other columns can be updated.
>
> Any help is very much appreciated.

31 Aug 2006 5:06 PM

jenks

Ahhhh...makes sense. Thanks

Show quote

"Ryan" wrote:

> Then what you want is not really a rollback, but a selective update.
>
> Write your trigger to re-update the table updating all columns, except
> Phone1, Phone2, Phone3, Fax to be taken from the inserted table, and the
> columns for Phone1, Phone2, Phone3, Fax to be taken from the deleted table.
>
> Ryan
> www.ddrs.net
>
> "jenks" wrote:
>
> > I am trying to create a trigger that rolls back updates to a specific columns
> > in a table under certain conditions. The area I am having trouble with is
> > only rolling back updates to these columns and allowing updates to other
> > columns in the table.
> >
> > The table is very simple
> >
> > Company Contact Address1 Address2 City State Zip Phone1 Phone2 Phone3 Fax
> >
> > If the updating user is <> 'dnccuser' and Phone1, Phone2, Phone3, Fax begin
> > with 'DNC', I want updates to Phone1, Phone2, Phone3, Fax to roll back but
> > all other columns can be updated.
> >
> > Any help is very much appreciated.

31 Aug 2006 5:44 PM

Arnie Rowland

At the point of the trigger firing, all the data has been updated. You only
want to 're-update' (or undo) the 4 phone related columns if the update was
initialized by dnccuser.

IF ( session_user() = 'dnccuser' )
{update the table with the values from deleted }
ELSE
RETURN

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Ryan" <R***@discussions.microsoft.com> wrote in message
news:1BFB9565-BFE3-42C3-B482-A849A581C90F@microsoft.com...
> Then what you want is not really a rollback, but a selective update.
>
> Write your trigger to re-update the table updating all columns, except
> Phone1, Phone2, Phone3, Fax to be taken from the inserted table, and the
> columns for Phone1, Phone2, Phone3, Fax to be taken from the deleted
> table.
>
> Ryan
> www.ddrs.net
>
> "jenks" wrote:
>
>> I am trying to create a trigger that rolls back updates to a specific
>> columns
>> in a table under certain conditions. The area I am having trouble with is
>> only rolling back updates to these columns and allowing updates to other
>> columns in the table.
>>
>> The table is very simple
>>
>> Company Contact Address1 Address2 City State Zip Phone1 Phone2 Phone3 Fax
>>
>> If the updating user is <> 'dnccuser' and Phone1, Phone2, Phone3, Fax
>> begin
>> with 'DNC', I want updates to Phone1, Phone2, Phone3, Fax to roll back
>> but
>> all other columns can be updated.
>>
>> Any help is very much appreciated.

31 Aug 2006 5:17 PM

Vern Rabe

Or maybe an INSTEAD OF trigger, not an AFTER trigger. You would then only
modify the base table once, instead of modifying followed by conditionally
reversing.

HTH
Vern Rabe

Show quote

"jenks" wrote:

> I am trying to create a trigger that rolls back updates to a specific columns
> in a table under certain conditions. The area I am having trouble with is
> only rolling back updates to these columns and allowing updates to other
> columns in the table.
>
> The table is very simple
>
> Company Contact Address1 Address2 City State Zip Phone1 Phone2 Phone3 Fax
>
> If the updating user is <> 'dnccuser' and Phone1, Phone2, Phone3, Fax begin
> with 'DNC', I want updates to Phone1, Phone2, Phone3, Fax to roll back but
> all other columns can be updated.
>
> Any help is very much appreciated.