Solved

update trigger

Posted on 2013-06-10

Microsoft SQL Server

1 solution

276 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,

Comment

Question by:swathi111

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

2 Comments

LVL 27

Accepted Solution

by:Zberteoc

Zberteoc earned 500 total points

ID: 392347392013-06-10

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

LVL 21

Expert Comment

by:Alpesh Patel

ID: 392373992013-06-11

SElect PeopleID from inserted

a trigger specific table.

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

Featured Post

Question has a verified solution.

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

Installing SQL Server vNext on Linux: Part 1 (Prerequisites)

Article by: Yashwant

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.

How to dynamically return a table structure using a Stored Procedure

Article by: Ryan Chong

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…

Executing a SQL Server Function from Within Access

Video by: TechMommy

Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

Creating and Executing a SQL Pass-thru Query

Video by: TechMommy

Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…