Solved

update trigger

Posted on 2013-06-10
2
267 Views
Last Modified: 2013-07-19
Hi,

I want to write one trigger on insert and update. Please find below trigger, it is working in case of insert but it is not working in case of update.

ALTER trigger [dbo].[TR_AIU_People_SearchName] on [dbo].[People] AFTER INSERT, UPDATE
as
begin
      update dbo.People
            set SearchName=(ltrim(rtrim(PrefFirstName)) + ltrim(rtrim(PrefMidName)) + ltrim(rtrim(PrefLastName)) + PrefNameLocal
                                    + ltrim(rtrim(LeglFirstName)) + ltrim(rtrim(LeglMidName)) + ltrim(rtrim(LeglLastName)) + LeglNameLocal
                                    + ltrim(rtrim(PrimFirstName)) + ltrim(rtrim(PrimMidName)) + ltrim(rtrim(PrimLastName)) + PrimNameLocal)
      where PeopleId = PeopleID
end;

I am using sql server 11.0. Please check where PeopleID = PeopleID here what should i give? i tried with SCOPE_Identity() and @@IDENTITY but it is not working.

Thanks,
0
Comment
Question by:swathi111
2 Comments
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39234739
You have to use the inserted system trigger table in order to make it work:

ALTER trigger [dbo].[TR_AIU_People_SearchName] on [dbo].[People] AFTER INSERT, UPDATE
as 
begin
	update p 
		set SearchName=(ltrim(rtrim(i.PrefFirstName)) + ltrim(rtrim(i.PrefMidName)) + ltrim(rtrim(i.PrefLastName)) + i.PrefNameLocal
						+ ltrim(rtrim(i.LeglFirstName)) + ltrim(rtrim(i.LeglMidName)) + ltrim(rtrim(i.LeglLastName)) + i.LeglNameLocal
						+ ltrim(rtrim(i.PrimFirstName)) + ltrim(rtrim(i.PrimMidName)) + ltrim(rtrim(i.PrimLastName)) + i.PrimNameLocal) 
	from
		dbo.People p
		inner join inserted i
			on i.PeopleId = p.PeopleID 
end;

Open in new window

You can see examples here: http://www.youtube.com/watch?v=L6cSzELlSy4
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 39237399
SElect PeopleID from inserted


a trigger specific table.


for Insert and update table is inserted
and for Delete trigger table is deleted.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Truncate vs Delete 63 88
MS SQL 2014 get SPIDs of users 6 26
Complex SQL 10 33
SQL JOIN 6 35
In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now