December 15, 2015 at 3:58 pm
PJ_SQL (12/15/2015)
This is my trigger:CREATE TRIGGER TRG_A
ON _A
AFTER INSERT, UPDATE
AS
insert into _B
SELECT * FROM inserted
where c is not null
How do I incorporate in this?
Thank you.
That's up to you, really.
What data do you want to keep in table _B?
What is your PK (or UNIQUE key) on table _A?
Do you want to record the datetime when the change was made?
Do you want to have old and new values in a single record in table _B or in 2 separate ones?
When I do change tracing the trace table (_B) looks like this:
(
ChageDate datetime,
PK_Column(s), -- as per table _A
[MonitoredColumn1_Old] [MonitoredColumn1 datatype],
[MonitoredColumn1_New] [MonitoredColumn1 datatype],
[MonitoredColumn2_Old] [MonitoredColumn2 datatype],
[MonitoredColumn2_New] [MonitoredColumn2 datatype],
... etc.
)
Is it how you want it?
_____________
Code for TallyGenerator
December 18, 2015 at 8:03 am
PJ_SQL (12/15/2015)
This is my trigger:CREATE TRIGGER TRG_A
ON _A
AFTER INSERT, UPDATE
AS
insert into _B
SELECT * FROM inserted
where c is not null
How do I incorporate in this?
Thank you.
Two things you desperately need to do:
1) Start using BOL, it is the documentation for sql server. Then read the entry on triggers. https://msdn.microsoft.com/en-us/library/ms189799.aspx
2) DO NOT put select * inside your trigger like that. You are asking for problems. Consider what happens when you change the schema for the base table...just don't use select * anywhere outside of an [NOT]EXISTS and you will do yourself a lot of favors. For inserts you really should always explicitly name the columns too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply