Solved

update trigger

Posted on 2013-06-10
2
268 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

919 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

16 Experts available now in Live!

Get 1:1 Help Now