April 3, 2012 at 8:12 am
I don't usually do triggers, but because my senior DBA is out on vacation, I need some help...
So here we go:
(Let me preface the conversation: The hold reason in customer table uses an empirical update. Instead of normal logic to trigger a modify user & timestamp, the application logic ONLY updates the hold reason directly from the application to the database when changed (bypassing update logic)… I know, I know… that’s not right, but until it gets fixed, I need a trigger to get around this…)
I have a CUSTOMER table and I have created ZZ_CUSTOMER_HOLD_REASON table. I need the trigger to do the following:
The trigger will be put on table CUSTOMER. Here are the columns in CUSTOMER that I need to grab as part of the trigger.
CUSTOMER.HOLD_REASON
CUSTOMER.CUSTOMER_ID
MODIFY_USER = get the USER_ID
MODIFY_TIMESTAMP = getdate()
ANY TIME the value in CUSTOMER.HOLD_REASON changes (this is important, ONLY when that value changes), grab the new CUSTOMER.HOLD_REASON, CUSTOMER.CUSTOMER_ID, the use USER_ID and GETDATE(); then insert that as a new record into ZZ_CUSTOMER_HOLD_REASON. I do not want to overwrite data in ZZ_CISTOMER_HOLD_REASON, just add a new line.
April 3, 2012 at 8:21 am
shoud get you close.
CREATE TRIGGER TriggerName
ON Table.Column
AFTER UPDATE --can also do AFTER INSERT, UPDATE if you want both
AS
INSERT INTO ZZ_CUSTOMER_HOLD_REASON
SELECT CUSTOMER.HOLD_REASON,CUSTOMER.CUSTOMER_ID,
[some way to get the user id you want, may need a join to somewhere], GETDATE()
GO
this is only to get you started in the right direction. for tested code can you post DDL for the tables involved, how you will get the user id and some sample data from both tables. for help pease see the link in my signature.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 3, 2012 at 8:23 am
would also have to build in a call to UPDATE() as well to ensure that it only fires on the update of a specific column
CREATE TRIGGER trigger
ON Schema.Table
AFTER UPDATE
AS
IF (UPDATE (column))
BEGIN
INSERT INTO
Schema.Table (Column, Column)
SELECT
Column, Column
FROM
inserted
END
April 3, 2012 at 8:28 am
I think you have been around long enough to know that what we need is the DDL for the tables (CREATE TABLE statements), sample data (INSERT INTO statements) for the tables, expected output based on the sample data.
April 3, 2012 at 8:50 am
Thank you capn.hector and anthony.green,
I didn't think it would be so easy. I thought there would be some kind of logic that would much more complicated... Sorry, I feel a little embarrassed. :unsure:
April 3, 2012 at 9:24 am
SQL_Enthusiast-AZ (4/3/2012)
Thank you capn.hector and anthony.green,I didn't think it would be so easy. I thought there would be some kind of logic that would much more complicated... Sorry, I feel a little embarrassed. :unsure:
yep the logic is very simple. just need the light shined on the right book some times.
glad we could help
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply