Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create a trigger on a column Expand / Collapse
Author
Message
Posted Wednesday, November 18, 2009 9:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 11:19 AM
Points: 75, Visits: 231
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]
[/i]
Post #820965
Posted Wednesday, November 18, 2009 10:38 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 29, 2010 2:48 AM
Points: 124, Visits: 275
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
Post #821023
Posted Wednesday, November 18, 2009 12:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 4:51 PM
Points: 11,264, Visits: 13,023
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
/*
This means the data in the desired column
changed.
*/
I.column_updated <> D.column_updated

END
ELSE -- insert
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
END





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #821067
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse