Trigger to update on insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger to update on insert

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2013-10-31 : 08:05:06

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-31 : 08:18:22

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-31 : 08:25:05

[code]
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
[/code]
PK is the primary key of Clusters table in above code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2013-10-31 : 08:25:30

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-31 : 08:28:49

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2013-10-31 : 08:28:51

Thanks Visakh. I see your solution is in line with what i need.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-31 : 08:30:25

cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2013-10-31 : 08:51:48

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-31 : 08:59:41

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2013-10-31 : 09:16:54

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-31 : 09:45:56

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources