First time building a SQL Trigger(HELP!)

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 2008 Forums

Transact-SQL (2008)

First time building a SQL Trigger(HELP!)

Author

Topic

tmcivery
Starting Member

46 Posts

Posted - 2011-02-22 : 14:37:30

Hey folks,

Here is my scenario. I have a single table(contact1). I need a trigger that would send the value(when inserted or updated) of field1 into field2, same table. I also need to concatenate a value to the beginning of the value sent to field2. For example: xx-value.

Any help would be appreciated!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-22 : 15:11:27

Use an INSTEAD OF trigger for this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tmcivery
Starting Member

46 Posts

Posted - 2011-02-22 : 15:26:51

This is what I have so far but I'm having a problem:

Create TRIGGER FieldUpdateTest
ON dbo.Contact1
AFTER INSERT,UPDATE
AS
IF UPDATE(DEAR)
BEGIN
DECLARE @KEY5 VARCHAR(35)
BEGIN
SET @KEY5 = (SELECT DEAR from Inserted)
INSERT INTO CONTACT1(KEY5)VALUES(@KEY5)
END
END

When I update the Dear field I'm given the following error:

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'recid', table 'GoldMine.dbo.CONTACT1'; column does not allow nulls. INSERT fails.
-------------------------------

Is the trigger trying to write a whole new line to the contact1 table? I was under the assumption it would just update the same line that fired the trigger. Any thoughts?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-22 : 15:27:42

It needs to be an INSTEAD OF trigger and not an AFTER trigger. Please read up on INSTEAD OF triggers.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tmcivery
Starting Member

46 Posts

Posted - 2011-02-22 : 19:48:56

This is what I've got so far:

Create TRIGGER InsteadTrigger on Contact1
INSTEAD OF Update
AS
SET NOCOUNT ON
IF UPDATE(DEAR)
BEGIN
UPDATE Contact1
SET Key5 = (select DEAR from inserted)
Where Recid = (Select Recid from inserted)

END
GO
---------------------------------

This manages to send anything entered into the "Dear" field into the "Key5" field but the entry that was placed into "Dear" is deleted the moment it is entered. Anyone have an idea why?

tmcivery
Starting Member

46 Posts

Posted - 2011-02-22 : 20:03:29

This seems to work. Thanks for the pointer Tara.

Create TRIGGER TriggerName on Contact1
INSTEAD OF Update
AS
SET NOCOUNT ON
IF UPDATE(DEAR)
BEGIN
UPDATE Contact1
SET Key5 = (select DEAR from inserted),
Dear = (select Dear from inserted)
Where Recid = (Select Recid from inserted)

END
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-22 : 21:09:31

you should use a INNER JOIN. Your trigger will not be able to handle multiple record being updated at the same time.


UPDATE c
SET    Key5 = i.Dear,
       Dear = i.Dear 
FROM   INSERTED i
       INNER JOIN Contact1 c    ON i.Recid = c.Recid

Why do you want to make both column the same value ?
Since Key5 and Dear always have the same value, why don't you make one of the column a Compute column ?

KH
[spoiler]Time is always against us[/spoiler]

tmcivery
Starting Member

46 Posts

Posted - 2011-02-22 : 22:31:36

Thanks for you reply. It turns out that I wasn't explained properly what they were looking for.

When a new record is added to the table they want the recid(unique key) placed in another field in the same table but concatenated with another value(ie. IM-9a8dfa777&*&&).

Unique key = recid
Field to be updated with concatenated value=key5

I'll start trying to figure this out on my own but I feel like I'm back at square one....bah.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-22 : 23:53:26

If i understand you correctly, you can still do it with computed column. See example below


create table #temp
(
	pk_col		varchar(10),
	somecol		int,
	another_col	as pk_col + '12345'
)

insert into #temp (pk_col, somecol) select 'ABC', 10
insert into #temp (pk_col, somecol) select 'DEF', 20

select	*
from	#temp

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources