Trigger question - not the horse :-)

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 question - not the horse :-)

Author

Topic

rstout
Starting Member

25 Posts

Posted - 2005-03-18 : 18:43:47

I have a table Test with 3 columes, c1, c2 and 3c
I need to make a trigger when a recored is added to only 3c it is copied to c1
It will be happening an append

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-18 : 18:46:48

So how far did you get? Did you look up CREATE TRIGGER in SQL Server Books Online?

Tara

rstout
Starting Member

25 Posts

Posted - 2005-03-18 : 19:10:38

Yea but didn't understand a thing. I am not sure how to know what record was added or updated to modify

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-18 : 19:34:50

You should read the topic "Using the inserted and deleted Tables" in BOL.

quote:
Originally posted by rstout

Yea but didn't understand a thing. I am not sure how to know what record was added or updated to modify

CODO ERGO SUM

rstout
Starting Member

25 Posts

Posted - 2005-03-28 : 21:34:54

OK.. Yes I understand how to add the trigger but how do I know the record that was added? Is that implied in the trigger?
This would be the upate string
UPDATE test SET test.test2 = test.test1

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-28 : 21:45:18

As I said before, you should read the topic "Using the inserted and deleted Tables" in SQL Server Books Online.

quote:
Originally posted by rstout

OK.. Yes I understand how to add the trigger but how do I know the record that was added? Is that implied in the trigger?
This would be the upate string
UPDATE test SET test.test2 = test.test1

CODO ERGO SUM

rstout
Starting Member

25 Posts

Posted - 2005-03-28 : 23:33:46

OK I read it and still have some questions. There is no key in the table so how do I determaind the acual record to update?
ON i.title_id = t.title_id
WHERE t.title_id = i.title_id

hacked up sample code....
CREATE TRIGGER Copy_Test2_to_Test1_Trigger
ON test
INSTEAD OF update
AS

IF @@rowcount = 0 RETURN
IF UPDATE(title_id) BEGIN
PRINT 'updates to primary key title_id are not allowed'
RETURN
END

IF UPDATE (Test2)
BEGIN
IF i.test1 !<> ''
BEGIN
PRINT 'Do not update Test1'
RETURN
END

UPDATE t
SET Test1 = i.Test2
FROM Test t join inserted i
ON i.title_id = t.title_id
WHERE t.title_id = i.title_id
END
RETURN

rstout
Starting Member

25 Posts

Posted - 2005-03-29 : 00:10:32

Got more info....

SC, SN, and DY are the keys in the table named ATT
The field that I am monitoring is A1 and want it copied to AL if AL is not empyt or Null
AL and A1 are valchar
SC,DY are smallint
SN is int

here is the new trigger
CREATE TRIGGER Copy_A1_to_AL_Trigger
ON ATT
INSTEAD OF update
AS

IF @@rowcount = 0 RETURN

IF UPDATE (A1)
BEGIN
IF i.AL != ''
BEGIN
PRINT 'Do not update.'
RETURN
END

UPDATE t
SET AL = i.A1
FROM ATT t join inserted i
ON t.SC = i.SC AND
t.SN = i.SN AND
t.DY = i.DY
WHERE t.SC = i.SC AND
t.SN = i.SN AND
t.DY = i.DY
END
RETURN

I get the following error in query anylzer
Server: Msg 107, Level 16, State 2, Procedure Copy_A1_to_AL_Trigger, Line 10
The column prefix 'i' does not match with a table name or alias name used in the query.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-03-29 : 01:09:54

try using inserted instead of the alias in your join

HTH

--------------------
keeping it simple...

rstout
Starting Member

25 Posts

Posted - 2005-03-29 : 13:02:47

I am still new at this so... you are saying to have it like this?

IF inserted.AL != ''

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

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

>>> There is no key in the table <<<

You have bigger issues than how to write a trigger.

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)

rstout
Starting Member

25 Posts

Posted - 2005-03-29 : 22:49:24

the key thing is not a problem any more

now it is just the problem above

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources