SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help With Update trigger


Help With Update trigger

Author
Message
hnmahomed
hnmahomed
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 17
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 .
Sean Lange
Sean Lange
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33537 Visits: 17681
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
PiMané
PiMané
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1444 Visits: 1347

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





If you need to work better, try working less...
hnmahomed
hnmahomed
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 17
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.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.


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 .
Sean Lange
Sean Lange
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33537 Visits: 17681
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
hnmahomed
hnmahomed
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 17
Thank you guys Help is much appreciated
I have applied the example

To my scenario and it works perfectly .
PiMané
PiMané
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1444 Visits: 1347
Glad to help anytime... Smile



If you need to work better, try working less...
PiMané
PiMané
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1444 Visits: 1347
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....



If you need to work better, try working less...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search