Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

update trigger

Posted on 2013-06-10

Microsoft SQL Server

1 solution

Medium Priority

299 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 2000 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

Understand What is Row Level Security in SQL Server 2016

Article by: Daniel

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works

Microsoft SQL Server

SSMS failed to connect to SSIS

Article by: Vitor

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …

Connecting To SQL Server From Oracle Using Heterogeneous Services Generic Gateway

Video by: Steve

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

Dealing with Dates: Data Types and String Conversions (SQL Server)

Video by: Steve

Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.