Need help creating a trigger? - 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)

Need help creating a trigger?

Author

Topic

Gopher
Yak Posting Veteran

83 Posts

Posted - 2006-08-24 : 07:02:09

Hi All

I am news to creating triggers and am not sure how write one.

What I am trying to do is intercept an insert statement and change one of the values.

For example the statement:
insert into usertable (id,name,location) values('1','Gopher','UK')

I want change the location field to 'England'

Can anyone help?

Thanks in advance

CREATE TRIGGER tr_SetCoversheet
ON FEN_USR
FOR INSERT
AS

@CoverSheet VARCHAR(15)

@CoverSheet = 'NoCover.cs'

INSERT FEN_USR values (@CoverSheet)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-24 : 07:06:32

you need to use instead of trigger and not after/for trigger.

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

blog thingie: http://weblogs.sqlteam.com/mladenp

Gopher
Yak Posting Veteran

83 Posts

Posted - 2006-08-24 : 07:09:27

Thanks for your help - do you have an example?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 07:12:50

Put this in your trigger

UPDATE		yt
SET		yt.Location = 'England'
FROM		YourTable yt
INNER JOIN	inserted i ON i.ID = yt.ID
WHERE		yt.Location = 'UK'

And of course to make the trigger not recursive, check for UPDATED in BOL.

Or check the existence of DELETED first. OR just make the trigger an INSERT trigger.

Peter Larsson
Helsingborg, Sweden

Gopher
Yak Posting Veteran

83 Posts

Posted - 2006-08-24 : 07:24:46

Sorry I should have been clearer - but the 'Location' will not always be 'UK' it could be different. The trigger also has change the insert statement before it goes in the DB.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-24 : 07:35:31

quote:
Originally posted by Gopher

Sorry I should have been clearer - but the 'Location' will not always be 'UK' it could be different. The trigger also has change the insert statement before it goes in the DB.

what is the look up table?? where you will get UK = "England"??

Chirag

Gopher
Yak Posting Veteran

83 Posts

Posted - 2006-08-24 : 07:38:28

It is actually from some software - I just was to change before it inserts.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 07:40:20

You can't do that. How are you going to intercept the insert statement and alter the data, before it is stored in the database?

What you CAN do, is to create a trigger and update the data after it is inserted in the database.

Peter Larsson
Helsingborg, Sweden

Gopher
Yak Posting Veteran

83 Posts

Posted - 2006-08-24 : 07:43:04

CREATE TRIGGER ChangeLocation on usertable
INSTEAD OF INSERT
AS
BEGIN

INSERT INTO usertable(location) values('England')

END
GO

Would that work?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-24 : 07:46:09

quote:
You can't do that

Instead of trigger can do a trick over here..


Create TAble Test
(
i int 
)
GO
CREATE TRIGGER InsteadTrigger on Test
INSTEAD OF INSERT
AS
BEGIN
  
  INSERT INTO test
       SELECT 12
END
GO
Insert Into Test 
Select 1 

Select * from test

Drop Table Test

Chirag

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-24 : 07:48:37

quote:
Originally posted by Gopher

CREATE TRIGGER ChangeLocation on usertable
INSTEAD OF INSERT
AS
BEGIN

INSERT INTO usertable(location) values('England')

END
GO

Would that work?

No, then it will only insert the value in the column location and other column values will be set null or you may get some constraint error.

it should be somthing like this


CREATE TRIGGER ChangeLocation on usertable
INSTEAD OF INSERT
AS
BEGIN
 
  insert into usertable (id,name,location) 
  Select id,name,'England' From Inserted

END
GO

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources