Query - 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)

Query

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-01-27 : 14:28:40

[code]
I want a trigger which should update prvaddress1 if the address1 was updated with new address.

Example:

stu_id sname address1 prvaddress1
------ ----- -------- ------------
1 sam Newyork

When a address1 has been updated DC it previous address (prvaddress1) should be updated to NewYork

stu_id sname address1 prvaddress1
------ ----- -------- ------------
1 sam DC Newyork

Thanks for your help in advance
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-27 : 14:34:56

CREATE TRIGGER dbo.trgTable1
ON dbo.Table1
FOR UPDATE
AS

SET NOCOUNT ON

UPDATE x
SET x.prvAddress1 = d.address1
FROM dbo.Table1 AS x
INNER JOIN deleted AS d ON d.stu_id = x.stu_id

N 56°04'39.26"
E 12°55'05.63"

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-01-27 : 16:07:04

Thanks a lot Peso

I want the updates whenever there is change in the
address1 value however with the below trigger it is updating whenever the other column updates

Ex: sname has updated then also the trigger fires and prvaddress1 has been updated as per the req which should not happen.

Please help for above scenario.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-27 : 16:54:26

Did you try to find the missing piece of information in Books Online?

CREATE TRIGGER dbo.trgTable1
ON dbo.Table1
FOR UPDATE
AS

SET NOCOUNT ON

IF UPDATE(address1)
UPDATE x
SET x.prvAddress1 = d.address1
FROM dbo.Table1 AS x
INNER JOIN deleted AS d ON d.stu_id = x.stu_id
INNER JOIN inserted AS i ON i.stu_id = x.stu_id
WHERE i.address1 <> d.address1
OR d.address1 IS NULL

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources