SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trigger to update on insert
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WJHamel
Aged Yak Warrior

USA
644 Posts

Posted - 10/31/2013 :  08:05:06  Show Profile  Reply with Quote
a wee nudge please.

I have several tables that i need to add a trigger to. One such table is "Clusters". This table contains a column called ClusterFKey which should be the same value as the AutoID column in the Serverdata table (same db) and should be populated every time a record is added to the Clusters table. The value should be joined on the servername column's value in both the Serverdata and Clusters table.

So, when a record is added to Clusters, the ClusterFkey should equal the AutoID key in Serverdata where Clusters.servername = Serverdata.server and Clusters.ClusterFkey is null.

Yeah, so i got the idea, but that is as far as i am currently.

Thank you in advance for your help.

James K
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 10/31/2013 :  08:18:22  Show Profile  Reply with Quote
The trigger would be something along these lines. Don't you also need to handle the cases where data is updated or deleted inClusters table?
CREATE TRIGGER dbo.ServerDataTrigger
ON dbo.Clusters
FOR INSERT 
AS
INSERT INTO ServerData
(AutoId)
SELECT ClusterFKey FROM INSERTED;
GO
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/31/2013 :  08:25:05  Show Profile  Reply with Quote

CREATE TRIGGER dbo.ServerDataTrigger
ON dbo.Clusters
FOR INSERT 
AS
UPDATE  c
SET c.ClusterFKey = sd.AutoID
FROM Clusters c 
INNER JOIN INSERTED i
ON i.PK = c.PK
INNER JOIN Serverdata sd
ON sd.server = c.servername 
WHERE c.ClusterFkey is null
GO

PK is the primary key of Clusters table in above code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
644 Posts

Posted - 10/31/2013 :  08:25:30  Show Profile  Reply with Quote
Thank you, but wait. Isn't that backward?

I want the insert to happen on the Clusters table. This looks like it's inserting into the serverdata table, which would be wrong.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/31/2013 :  08:28:49  Show Profile  Reply with Quote
quote:
Originally posted by WJHamel

Thank you, but wait. Isn't that backward?

I want the insert to happen on the Clusters table. This looks like it's inserting into the serverdata table, which would be wrong.


See my posted suggestion
I think thats what you're after!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
644 Posts

Posted - 10/31/2013 :  08:28:51  Show Profile  Reply with Quote
Thanks Visakh. I see your solution is in line with what i need.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/31/2013 :  08:30:25  Show Profile  Reply with Quote
cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
644 Posts

Posted - 10/31/2013 :  08:51:48  Show Profile  Reply with Quote
i'm confused as to what the Inner Join Inserted line is actually doing. Can you explain?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/31/2013 :  08:59:41  Show Profile  Reply with Quote
quote:
Originally posted by WJHamel

i'm confused as to what the Inner Join Inserted line is actually doing. Can you explain?


It will make sure only the records that were a part of recent INSERT operation are picked for update operation

Internally SQL server makes use of two temporary tables (INSERTED & DELETED) for performing INSERT,UPDATE & DELETE operations
INSERT - In this case INSERTED table will contain new rows that were inserted & DELETED table unused
UPDATE - In this case INSERTED table will contain updated (modified) values and DELETED table will contain old (replaced) values
DELETE - In this case DELETED tables will contain rows which were deleted and INSERTED table is unused.

These tables assumes the structure of main table on which DML operations take place (in this case same as table Clusters)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
644 Posts

Posted - 10/31/2013 :  09:16:54  Show Profile  Reply with Quote
So the WHERE c.ClusterFkey is null portion doesn't take care of that by itself?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/31/2013 :  09:45:56  Show Profile  Reply with Quote
quote:
Originally posted by WJHamel

So the WHERE c.ClusterFkey is null portion doesn't take care of that by itself?


nope not always
it will take even the cases where ClusterFkey remained null for a previously inserted record. do we want to consider them as well?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000