Creating a trigger for the row being updated

Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tuxTry:
update m
set
UpdatedDate = getdate ()
, UpdatedBy = CURRENT_USER
from
MyTable m
join
inserted i on i.ItemCode = m.ItemCode
join
deleted d on d.ItemCode = i.ItemCode
where
i.ShortDescription <> d.ShortDescription
or i.Quantity <> d.Quantity
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux|||leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>|||The problem with this code is that is does not exactly meet the spec:
'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
fields when either the "ShortDescription" or "Quantity" fields get updated.'
IOW, if both of these columns are set to their original values, the you are
updating the UpdatedDate and UpdatedBy columns, which you don't want to do.
You need to join also on the deleted virtual table and compare the values in
inserted vs. deleted.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>|||Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
>> Hello group,
>> I have a question regarding an update trigger.
>> For example, I have the following fields in a table..
>> ItemCode
>> ShortDescription
>> Quantity
>> UpdatedDate
>> UpdatedBy
>> I want to create a trigger that updates the "UpdatedDate" and
>> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
>> fields get updated.
>> I have figured out how to use the "bitwise" operators so as to target
>> only columns 2 and 3 for chages, however, telling the trigger to only
>> update the records which has been changed is proving to be a
>> challenge.
>> Thanks in advance
>> Leenux_tux
>|||That emphasizes the fact that a PK should never change. There are
exceptions, though.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uh5I%23VIqHHA.3892@.TK2MSFTNGP05.phx.gbl...
Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
>> Hello group,
>> I have a question regarding an update trigger.
>> For example, I have the following fields in a table..
>> ItemCode
>> ShortDescription
>> Quantity
>> UpdatedDate
>> UpdatedBy
>> I want to create a trigger that updates the "UpdatedDate" and
>> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
>> fields get updated.
>> I have figured out how to use the "bitwise" operators so as to target
>> only columns 2 and 3 for chages, however, telling the trigger to only
>> update the records which has been changed is proving to be a
>> challenge.
>> Thanks in advance
>> Leenux_tux
>