Trigger dummy - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Trigger dummy

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2006-02-23 : 15:45:26

I have the following Trigger on the MeterData table...

CREATE TRIGGER [MeterData_EOW_Update] ON [dbo].[meterdata] 
FOR INSERT, UPDATE
AS
IF UPDATE([TimeStamp])
	UPDATE MeterData SET EOW_Date = DateAdd(day, -2, DateAdd(week, DateDiff(week, 0, MeterData.[TimeStamp])+1, 0))

The problem is that it updates the entire table every time. How do I set this up to only update the rows that are being inserted/updated?

TIA!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-23 : 15:47:25

You need to join to the inserted table. Join on the PK column of MeterData.

UPDATE m
SET EOW_Date = DateAdd(day, -2, DateAdd(week, DateDiff(week, 0, [TimeStamp])+1, 0)
FROM MeterData m
INNER JOIN inserted i
ON m.?? = i.??

http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

Tara Kizer
aka tduggan

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2006-02-23 : 15:58:21

Oh boy, do I feel dumb. I did not realize that the inserted logical table was available for update.

Thanks!

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2006-02-23 : 16:04:33

One other stupid question. How come you guys usually substitue the actual table name with another reference (e.g. MeterData = m). Is this just so you don't have to type as much, or is there another benefit I don't know about?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-23 : 16:10:59

Typing is definately one benefit but also I find using table aliases much easier to read. In terms of performance I don't believe it will affect the execution plan in any way.

Be One with the Optimizer
TG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-23 : 16:23:58

Readability is the reason I do it.

Tara Kizer
aka tduggan

Kristen
Test

22859 Posts

Posted - 2006-02-24 : 00:47:53

FWIW I always put the "AS" word in ... reduces the risk of some other typo getting in the way.

SELECT ColA ColB
FROM MyTableA MyTableB

is actually

SELECT ColA AS ColB
FROM MyTableA AS MyTableB

whereas lets assume what I meant was

SELECT ColA , ColB
FROM MyTableA , MyTableB

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-24 : 13:05:05

I only use AS for columns. Using the AS for tables throws me off when reading the query. Using a very short table alias pretty much eliminates the risk that you mention.

Tara Kizer
aka tduggan

Kristen
Test

22859 Posts

Posted - 2006-02-24 : 13:59:40

Hadn't thought of that Tara, its a good point as we tend to use single character table aliases.

For columns we are in the habit of doing

SELECT [DisplayName] = ColumnName

rather that the "AS" ALIAS thing as it makes sure that the [DisplayName] is visible, and not off the right hand edge of the screen (e.g. when doing CASE WHEN ... long stuff ... THEN ... ELSE ... END as DisplayName)

But that's all largely a question of style, and down to personal preference, and being consistent!

I've spent nearly a year getting ready to get a really good version control system in place; I embarked on switching all the legacy edits over to it in early January and we've been using it in anger since then, and we are just THRILLED with it. I just need automatic builds of QA now and I'll be happy! (its the meta data in the DEV DB that is slowing me up, not the Sprocs and DDL changes) ...

Sorry, rambling on on a Friday evening. I ought to go get a glass of wine and chill out. Had a really exciting day, so I'm a bit hyper!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources