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

Help With Update trigger Expand / Collapse
Author
Message
Posted Wednesday, September 19, 2012 5:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 1:35 AM
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 .
Post #1361271
Posted Wednesday, September 19, 2012 9:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
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 Moden's 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)
Post #1361420
Posted Wednesday, September 19, 2012 9:27 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 19, 2014 10:10 AM
Points: 526, Visits: 1,163
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...
Post #1361434
Posted Wednesday, September 19, 2012 10:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 1:35 AM
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 .



Post #1361501
Posted Wednesday, September 19, 2012 1:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
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 Moden's 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)
Post #1361582
Posted Thursday, September 20, 2012 12:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 1:35 AM
Points: 4, Visits: 17
Thank you guys Help is much appreciated
I have applied the example

To my scenario and it works perfectly .
Post #1361757
Posted Thursday, September 20, 2012 2:29 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 19, 2014 10:10 AM
Points: 526, Visits: 1,163
Glad to help anytime... :)




If you need to work better, try working less...
Post #1361786
Posted Thursday, September 20, 2012 5:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:45 PM
Points: 1,945, Visits: 3,185
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.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1361884
Posted Thursday, September 20, 2012 5:58 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 19, 2014 10:10 AM
Points: 526, Visits: 1,163
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...
Post #1361892
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse