Create a trigger on a column

Question

Post reply

Create a trigger on a column

Stephen crocker

SSCrazy

Points: 2512
More actions
November 18, 2009 at 9:49 am

#137283

i have the following trigger created on a table, I need to know if it's possible and how I would do this. what i need to know is if a column is updated then fire the trigger, the insert part works fine as it stands now. but I don't want it to create the new record in the table the trigger is writing to if any column is updated on the record.
I hope this makes since.
here is the trigger statement
ALTER trigger [dbo].[badge_Print_history] on
[dbo].[ev_registrant]
for Insert, update
as
insert [dbo].[client_gcsaa_badge_print_history]
(
s10_reg_badge_print_date,
s10_reg_cst_key,
s10_reg_key,
s10_reg_evt_key,
s10_reg_add_user,
s10_reg_change_user,
s10_reg_change_date,
s10_reg_delete_flag,
s10_reg_ivd_key
)
Select
reg_badge_print_date,
reg_cst_key,
reg_key,
reg_evt_key,
reg_add_user,
reg_change_user,
reg_change_date,
reg_delete_flag,
reg_ivd_key
from Inserted[/i]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jus SSCrazy Points: 2536 More actions · Answer 1

Alter the trigger to work only for Insert (remove update).

Change for Insert, update to for Insert.

You create another trigger for update if you want any other transaction to happen on update..

--Jus

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Do want to log the update if the data in the column does not change. For example:

Update Table

Set column = column

Will cause an update trigger to fire even if you use the If UPDATE(Column) syntax.

My recommendation would be something along these lines:

ALTER trigger [dbo].[badge_Print_history] on [dbo].[ev_registrant]

for Insert, update

AS

/*

This should only return rows that are updates.

*/

IF EXISTS(SELECT 1 FROM deleted AS D JOIN inserted I ON D.primary_key = I.primary_key)

BEGIN

INSERT [dbo].[client_gcsaa_badge_print_history]

(

s10_reg_badge_print_date,

s10_reg_cst_key,

s10_reg_key,

s10_reg_evt_key,

s10_reg_add_user,

s10_reg_change_user,

s10_reg_change_date,

s10_reg_delete_flag,

s10_reg_ivd_key

)

Select

reg_badge_print_date,

reg_cst_key,

reg_key,

reg_evt_key,

reg_add_user,

reg_change_user,

reg_change_date,

reg_delete_flag,

reg_ivd_key

from

Inserted AS I JOIN

deleted AS D ON

I.primary_key = D.primary_key AND