SQL Server Trigger SQL Help - 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 2005 Forums

Transact-SQL (2005)

SQL Server Trigger SQL Help

Author

Topic

hummy
Starting Member

32 Posts

Posted - 2008-07-04 : 08:39:59

Hi All.

I would like some assistance with creating a trigger on one of my SQL Server tables. BEing from an oracle background, i would know how to do it in Oracle but in SQL Server it is all fairly new to me.

This is what i am wanting to do.

We insert alot of records in one go into a particular table. And i'm wanting the date that is inserted as part of that record to be updated by 1 day.

This is what i have so far:

create trigger [dbo].[add1day] on [dbo].[VRP_CUSTOM_TABLE_1]
for insert
as
--Add 1 day to the Forms Recieved On Column
update VRP_CUSTOM_TABLE_1
set [CUSTOM TABLE 1 FORMS RECEIVED ON] = [CUSTOM TABLE 1 FORMS RECEIVED ON] +1

However what i forgot, was that this trigger updates EVERY record each time 1 record is inserted. I only want it to update the inserted record.

The unique key on the table is CONTACTID. What i'm not sure how to do is specify ther where clause to use the inserted CONTACTID field.

Any help much appreciated. t

THanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-04 : 09:03:36

in case of insert: the inserted data is at runtime of the trigger in a table named inserted.
inserted is exactly like your triggered table.

in case of delete: the table is named deleted.

in case of update:
old data is in deleted, new data is in inserted.

Greetings
Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

hummy
Starting Member

32 Posts

Posted - 2008-07-04 : 09:11:53

Thanks for replying.

I'm not sure i entirley understand your comment.

But are you saying that it should be the following:

create trigger [dbo].[add1day] on [dbo].[VRP_CUSTOM_TABLE_1]
for insert
as
--Add 1 day to the Forms Recieved On Column
update VRP_CUSTOM_TABLE_1
set [CUSTOM TABLE 1 FORMS RECEIVED ON] = [CUSTOM TABLE 1 FORMS RECEIVED ON] +1
where contactid = contactid

So the last contactid will be replaced by the actual value that was inserted?
?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-04 : 09:31:44

please test this:

update T1
set [CUSTOM TABLE 1 FORMS RECEIVED ON] = [CUSTOM TABLE 1 FORMS RECEIVED ON] +1
from VRP_CUSTOM_TABLE_1 T1,
inserted i
where i.contactid = T1.contactid

There are 10 types of people in the world: Those who understand binary, and those who don't...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-04 : 09:43:11

Hi Hummy,

Basically the trigger can reference some runtime virtual tables called [inserted] and [deleted]

So inside your trigger you could reference the [inserted] just as if it was any other table -- the [inserted] table holds an exact record of the rows you have inserted into the target table.

[deleted] works the same way except, obviously, for deleted rows.

Charlie.

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-04 : 09:48:26

Something like...


UPDATE t1
SET
    [column] = DATEADD(DAY, 1, [column])
FROM
    yourTable t1
    JOIN inserted i ON i.[contactId] = t1.[contactId]

-------------
Charlie

hummy
Starting Member

32 Posts

Posted - 2008-07-04 : 09:52:39

THanks for your answers....

this seems to do the trick:

update T1
set T1.[CUSTOM TABLE 1 FORMS RECEIVED ON] = T1.[CUSTOM TABLE 1 FORMS RECEIVED ON] +1
from VRP_CUSTOM_TABLE_1 T1,
inserted i
where T1.[CUSTOM TABLE 1 CONTACTID] = i.[CUSTOM TABLE 1 CONTACTID]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-04 : 09:53:43

Sorry my example above won't work because you have to specify the table.

This is a working example:



CREATE TABLE foo (
	[Id] INT IDENTITY (1,1)
	, [field1] DATETIME
	, [field2] VARCHAR(50)
	)

CREATE TRIGGER add1Dat ON foo FOR INSERT AS
UPDATE t
SET
	t.[field1] = DATEADD(DAY, 1, t.[field1])
FROM 
	foo t
	JOIN inserted i ON i.[Id] = t.[Id]

	
INSERT INTO foo SELECT '2008-01-01' , 'Hello Foo'

SELECT * FROM foo

-------------
Charlie

hummy
Starting Member

32 Posts

Posted - 2008-07-04 : 10:02:23

Thank you again for your help.

What wonderful people exist here.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources