September 19, 2012 at 5:42 am
Hi There
I require some assistance or direction with a update Trigger
I have 2 tables Client and Client 2
I need to update values in Client2 everytime specific values are changed in Client
Client Columns are Name,Post1,Post2,Post3,Post4,Post5,PostPC,DCLink,MainAccLink ----plus more
Client2 columns are Name,Post1,Post2,Post3,Post4,Post5,PostPC,CLLink,MainAccLink
CLlink = DClink
What I need to achieve is that when ever a change in Client occurs where the MainAccLink = 0
it must Update Client2
And if a new record is added to Client where the MainAccLink = 0 it must add that to Client2
What I have so far is fragmented and not really working
I have a script that Inserts into client2
But I have to keep truncating the client2 table
INSERT INTO Client2 (Name,Post1,Post2,Post3,Post4,Post5,PostPC,DCREF,MainAccLink)
SELECT Name,Post1,Post2,Post3,Post4,Post5,PostPC,DCLink,MainAccLink
FROM Client
WHERE MainAccLink = 0
--select * From Client2
--Truncate Table Client2
And the update trigger is
ALTER TRIGGER [dbo].[Update_Client2]
ON [dbo].[Client]
AFTER UPDATE
AS
if ( (select [MainAccLink] from Client) != 0
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE i
SET i.Name = p.name
FROM dbo.Client as p
INNER JOIN Client2 AS i ON i.CLLink = p.DClink
END
But it doesnt work
Any help would be appreciated .
September 19, 2012 at 9:06 am
Hi and welcome to SSC!!! It is somewhat unclear exactly what you are trying to do but in your trigger you are not referencing the insert virtual table. http://msdn.microsoft.com/en-us/library/ms191300.aspx
If you need specific help with coding your trigger we would need some more details. See the first link in my signature for best practices when posting questions.
_______________________________________________________________
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/
September 19, 2012 at 9:27 am
CREATE TABLE actualData (ID INT NOT NULL, Col1 VARCHAR(10), Col2 VARCHAR(10))
CREATE TABLE historicalData (ID INT NOT NULL, Date DATETIME DEFAULT GETDATE(), Col1 VARCHAR(10), Col2 VARCHAR(10))
CREATE TRIGGER trg_actualData ON actualData FOR UPDATE
AS
INSERT INTO historicalData (ID, Col1, Col2) SELECT Id, Col1, Col2 FROM deleted
INSERT INTO actualData VALUES (1, 'test', '1rst')
UPDATE actualData SET Col2 = '2nd' WHERE ID = 1
UPDATE actualData SET Col2 = '3rd' WHERE ID = 1
SELECT * FROM actualData
SELECT * FROM historicalData
In this example you will insert every change on actualData in historicalData.
If you only want to store the last data from actualData then you have to change the trigger.
CREATE TABLE actualData1 (ID INT NOT NULL, Col1 VARCHAR(10), Col2 VARCHAR(10))
CREATE TABLE historicalData1 (ID INT NOT NULL, Date DATETIME DEFAULT GETDATE(), Col1 VARCHAR(10), Col2 VARCHAR(10))
CREATE TRIGGER trg_actualData1 ON actualData1 FOR UPDATE
AS
MERGE historicalData1 AS trg
USING (SELECT ID, Col1, Col2 FROM deleted) AS src (ID, Col1, Col2) ON src.ID = trg.ID
WHEN MATCHED THEN
UPDATE SET trg.Col1 = src.Col1, trg.Col2 = src.Col2
WHEN NOT MATCHED BY TARGET THEN
INSERT (Id, Col1, Col2) VALUES (ID, Col1, Col2);
INSERT INTO actualData1 VALUES (1, 'test', '1rst')
UPDATE actualData1 SET Col2 = '2nd' WHERE ID = 1
UPDATE actualData1 SET Col2 = '3rd' WHERE ID = 1
SELECT * FROM actualData1
SELECT * FROM historicalData1
September 19, 2012 at 10:39 am
Sean Lange (9/19/2012)
Hi and welcome to SSC!!! It is somewhat unclear exactly what you are trying to do but in your trigger you are not referencing the insert virtual table. http://msdn.microsoft.com/en-us/library/ms191300.aspxIf you need specific help with coding your trigger we would need some more details. See the first link in my signature for best practices when posting questions.
HI thank you
for the response I will be sure to attempt to Post in the correct format .
This is more a learning exercise for me , to try and broaden what little I know.
This is probably why it didn't come out quite clear
What I am trying to achieve is , creating a trigger that will do a update and a insert simultaneously into secondary table when data is changed in the Primary.
I the db that I have I have a main customer and a link customer
The main customer is designated with a '0' in the MainAccLink field.
DClink and CLlink are identity columns
Create table
CREATE TABLE [dbo].[Client](
[DCLink] [int] IDENTITY(1,1) NOT NULL,--Is an IDENTITY column on real table
[Name] [varchar](50) NULL,
[Post1] [varchar](50) NULL,
[Post2] [varchar](50) NULL,
[Post3] [varchar](50) NULL,
[Post4] [varchar](50) NULL,
[Post5] [varchar](50) NULL,
[PostPC] [varchar](50) NULL,
[DCREF] [int] NULL,
[MainAccLink] [int] NULL
CREATE TABLE [dbo].[Client2](
[CLLink] [int] IDENTITY(1,1) NOT NULL,--Is an IDENTITY column on real table
[Name] [varchar](50) NULL,
[Post1] [varchar](50) NULL,
[Post2] [varchar](50) NULL,
[Post3] [varchar](50) NULL,
[Post4] [varchar](50) NULL,
[Post5] [varchar](50) NULL,
[PostPC] [varchar](50) NULL,
[DCREF] [int] NULL,
[MainAccLink] [int] NULL
Insert into Client
(DCLink,Name,Post1,Post2,Post3,Post4,Post5,PostPC,CREF,MainAccLink)
select'3','LOUIS PETER','P.O BOX 20800','DURBAN','','','','4000','3','0', UNION ALL
select'4','BRUCE LYLE FAMILY ','P.O.BOX 557','BALITO','','','','4420','4','0', UNION ALL
select'5','LOWE GARET S.','18 PARKVIEW DRIVE ','MANORS','TOWN','','','3610','5','0', UNION ALL
select'6','ANNAMALAI L','PO BOX 6','BRAN CROSS','','','','2164','7','0', UNION ALL
select'7','CABE COLIN','P.O. BOX 750','WESTVILLE','','','','3630','8','0', UNION ALL
So those are the Tables
The Idea is if I edit a record in Client table say the name it must edit the Name in Client2
and
If I add to Client it must add to client 2.
I think the separate scripts in my first post would do it individually
I want to try to add a trigger on the Client Table so it updates automatically .
September 19, 2012 at 1:09 pm
Then the MERGE as the fine example already posted is exactly what you want. Here is the link to the BOL article about MERGE.
http://msdn.microsoft.com/en-us/library/bb510625.aspx
_______________________________________________________________
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/
September 20, 2012 at 12:46 am
Thank you guys Help is much appreciated
I have applied the example
To my scenario and it works perfectly .
September 20, 2012 at 2:29 am
Glad to help anytime... 🙂
September 20, 2012 at 5:58 am
CELKO (9/20/2012)
This is not how we do this in RDBMS. Your second table is a subset of the first. We would use a VIEW to get a subset. If there is any difference in the subset that is important, then it is modeled with scalar values in a column. That is called "The Information Principle"; it is one of Dr. Codd's 12 rules.Rows are not records; we do not have "links' in RDBMS; that is a term from network databases. your mindset is still locked back in punch cards and want to keep two tables to mimic decks of cards. We treat a virtual table (VIEW, CTE, derived tables, etc) as a table. We hate triggers and procedural code, which is more of that punch card mindset.
You can partition a table if some of the data needs to be accessed more than the rest. All you are getting are kludges to maintain a fundamentally bad schema design.
I'm just guessing here but he probably wants an historical table, to store data changes made on a row.
He could, obviously, use the same table and add a column "ActualRecord" and have a partition on that column where the value is 1 and 0 and always use the 1 for current records (build a view over it)...
But sometimes is best to have a table to store the data changes since it's only use for audit porpoises...
And in some SW solutions for small business companies partitioning can decrease performance since they only have SATA disks with no RAID or whatever....
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply