Good Trigger Reference Site? - 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)

Good Trigger Reference Site?

Author

Topic

gingerpits
Starting Member

7 Posts

Posted - 2005-09-28 : 17:29:44

I'm looking to update another field in the same record if a certain field gets updated. I am new to writing TRIGGERS and am wondering if anyone as a good reference Web site that would help me out. Thanks in advance.

Kristen
Test

22859 Posts

Posted - 2005-09-29 : 00:35:19

Hi gingerpits, Welcome to SQL Team!

"wondering if anyone as a good reference Web site that would help me out"

Errmmm ... that would have to be SQL Team!!

You probably need something like:


CREATE TRIGGER MyTrigger
ON dbo.MyTable
AFTER INSERT, UPDATE 
AS
BEGIN
    UPDATE U
    SET MyCalulatedColumn = MyOtherCol1 * MyOtherCol2
    FROM dbo.MyTable AS U
         JOIN inserted AS I
             ON I.MyPK = U.MyPK
END

You could also do it with a stored procedure, provided ALL your table updates go through the Stored Procedure. This will be faster than a trigger (which will have already saved the data, and then come back and have to update it).

You could also use a Computed Column in the table; this won't physically store the value, just calculate it whenever that column is referenced. I hate the things - they cause mayhem with other processes such as inserting through views and so on, but they do do what they say on the tin!

Or you could have a VIEW on the table that does the calculation - so you SELECT from the VIEW as-and-when you need the calculated field, and you can just use the table when you don't need it.

Or you could have a Function that makes the calculation, and use that in your SELECTs ONLY when you need that value (the Function will ensure that the logic is contained in once place so that it is used uniformly, and can easily be changed in the future if the "formula" needs to be changed.

What's the specific problem you are trying to solve? Tell me that and I'll choose one of these for you - or a different one!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-29 : 01:08:31

Kris, I'm interested to know "Will function faster than other approaches?"

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-29 : 01:11:28

Nah, don't reckon.

Well actually, that's not true. If you have to change the logic of the formula every hour it probably will be

EDIT: Actually I wonder? If the function just does


CREATE FUNCTION MyFunction(@MyCol1 int, @MyCol2 int)
RETURNS int
AS
BEGIN
    RETURN @MyCol1 * @MyCol2
END

might it just get converted to something "inline"? You'd still be doing the calculation at SELECT time, rather than just retrieving a column value (but that would be true of a COMPUTED column too).

Darn it! I'll have to try it. The update I'm running is going to run another 10 minutes for sure, then I've got to baby sit an even bigger one .... "I'll be back!"

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-29 : 01:33:00

Usually I do this type of calculation in Select query itself than using Computed column, view or Function

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-29 : 02:22:26

I wouldn't do that if it resulted in the calculation being repeated all over the place - too many places to fix if it needs changing, and always the worry it will be mis-typed in one of them!

Here's what I ran as a test. The Function is very slow (3x). The others are all pretty much the same. Didn't try a trigger, but that's just a small additional cost on the INSERT - so if the rate of SELECT : INSERT is high its probably irrelevant.

All resultsets were saved to file from Q.A. The first run was ignored, so the data was "in memory" for the subsequent runs, and I mentally averaged the runs - apart from the last one they all ran the same time on each repeat execution.


DROP TABLE dbo.TEMP_CalcTest
GO
SELECT	[MyCol1]=IdentityColumn, [MyCol2]=SmallishIntColumn
INTO	dbo.TEMP_CalcTest
FROM	dbo.MyTable

SELECT MIN(MyCol1), MAX(MyCol1), MIN(MyCol2), MAX(MyCol2), COUNT(*)
FROM	dbo.TEMP_CalcTest

-- ----------- ----------- ----------- ----------- ----------- 
-- 1193076     1283122     1           2           89760

DROP FUNCTION dbo.TEMP_MyFunction
GO
CREATE FUNCTION dbo.TEMP_MyFunction(@MyCol1 int, @MyCol2 int)
RETURNS int
AS
BEGIN
    RETURN @MyCol1 + @MyCol2
END


DECLARE @dtStart datetime
SELECT	@dtStart = GetDate()
SELECT	MyCol1, MyCol1 + MyCol2
FROM	dbo.TEMP_CalcTest
SELECT [Elapsed] = DATEDIFF(Millisecond, @dtStart, GetDate())

C:\2kill\TEMP1.rpt 1885100 bytes

(89760 row(s) affected)

Elapsed     
----------- 
580

DECLARE @dtStart datetime
SELECT	@dtStart = GetDate()
SELECT	MyCol1, dbo.TEMP_MyFunction(MyCol1, MyCol2)
FROM	dbo.TEMP_CalcTest
SELECT [Elapsed] = DATEDIFF(Millisecond, @dtStart, GetDate())

(89760 row(s) affected)

Elapsed     
----------- 
1516

C:\2kill\TEMP2.rpt 1885100 bytes

DROP VIEW dbo.TEMP_MyView
GO
CREATE VIEW dbo.TEMP_MyView
AS
SELECT	MyCol1, [MyCalcCol] = MyCol1 + MyCol2
FROM	dbo.TEMP_CalcTest
GO

DECLARE @dtStart datetime
SELECT	@dtStart = GetDate()
SELECT	MyCol1, MyCalcCol
FROM	dbo.TEMP_MyView
SELECT [Elapsed] = DATEDIFF(Millisecond, @dtStart, GetDate())

(89760 row(s) affected)

Elapsed     
----------- 
596

C:\2kill\TEMP3.rpt 1885099 bytes

DROP TABLE dbo.TEMP_CalcTest2
GO
CREATE TABLE dbo.TEMP_CalcTest2
(
	MyCol1	int NOT NULL,
	MyCol2	int NULL,
	MyCalcCol AS (MyCol1 + MyCol2)
)
GO

INSERT INTO dbo.TEMP_CalcTest2
SELECT	MyCol1, MyCol2
FROM	dbo.TEMP_CalcTest
GO

DECLARE @dtStart datetime
SELECT	@dtStart = GetDate()
SELECT	MyCol1, MyCalcCol
FROM	dbo.TEMP_CalcTest2
SELECT [Elapsed] = DATEDIFF(Millisecond, @dtStart, GetDate())


(89760 row(s) affected)

Elapsed     
----------- 
580

C:\2kill\TEMP4.rpt 1885099 bytes

DROP TABLE dbo.TEMP_CalcTest3
GO
CREATE TABLE dbo.TEMP_CalcTest3
(
	MyCol1	int NOT NULL,
	MyCol2	int NULL,
	MyCalcCol int NULL
)
GO

INSERT INTO dbo.TEMP_CalcTest3
SELECT	MyCol1, MyCol2, (MyCol1 + MyCol2)
FROM	dbo.TEMP_CalcTest
GO

DECLARE @dtStart datetime
SELECT	@dtStart = GetDate()
SELECT	MyCol1, MyCalcCol
FROM	dbo.TEMP_CalcTest3
SELECT [Elapsed] = DATEDIFF(Millisecond, @dtStart, GetDate())


(89760 row(s) affected)

Elapsed     
----------- 
Varied between 563 and 596 
(whereas the others were more consistent 
- server prob. having some other work to do)

C:\2kill\TEMP5.rpt 1885099 bytes

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-29 : 02:28:41

Well Done Kris. I appreciate your effort

Madhivanan

Failing to plan is Planning to fail

gingerpits
Starting Member

7 Posts

Posted - 2005-09-29 : 11:50:18

I am planning on using a function that strips a string of all non alhpa characters. Basically, if Name, City or State gets updated, then the trigger will fire to create this generic code for a user which consists of 4-4-2 string of each field respectively and updates the code field.

I didn't know how to actually write the trigger to update same row, different fields, but your reply will help me out, so thanks. Has anyone ever had aissues with using functions in triggers? these triggers are only returning calculated values and not any large recordsets.

cheers.
travis

gingerpits
Starting Member

7 Posts

Posted - 2005-09-29 : 12:08:04

so basically, I have come up with this:

IF UPDATE(txtName)
BEGIN
UPDATE tblMYTABLE
SET tblMYTABLE.txtCode = LEFT(dbo.RemoveNonAlphas(inserted.txtName),4) + LEFT(dbo.RemoveNonAlphas(inserted.txtCity),4) + inserted.txtST
FROM tblMYTABLE, inserted
WHERE inserted.nmbID = tblMYTABLE.nmbID
END

and I will create a statement for the City and State(ST) fields also and will put it on tblMYTABLE

Kristen
Test

22859 Posts

Posted - 2005-09-29 : 12:17:12

"Well Done Kris. I appreciate your effort"

Well ... I had just pasted a 20MB XML file into an ad hoc query in Q.A., which then needed to upsquirt to the server along our 256K "up" pipe - before then falling over witht he first syntax error. So I had a little time to kill!

"so basically, I have come up with this"

Looks fine to me at a quick glance.

"txtST" and "txtName" are NOT TEXT datatype are they? If so you'll have trouble. varchar/nvarchar will be fine.

I'd prefer to see a JOIN rather than

FROM tblMYTABLE, inserted
WHERE inserted.nmbID = tblMYTABLE.nmbID

'coz that's SO '60's

but that's me done!.

Kristen

gingerpits
Starting Member

7 Posts

Posted - 2005-09-29 : 14:52:04

yeah, they are varchar. I'm just like to type the word "text". i'll make sure to tell my friend you called him old. :) thanks.

Kristen
Test

22859 Posts

Posted - 2005-09-29 : 16:00:06

"i'll make sure to tell my friend you called him old"

Hey! I remember the 60's ... unfortunately I was way too young for the "free love" bit :-(

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-29 : 16:02:15

damn...

Go with the flow & have fun! Else fight the flow