Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server trigger
hello experts, i want to be able to insert the name of the column that was modified into another table using after triggers.
USE [Test]
GO
/****** Object:  Trigger [dbo].[trgUpdate]    Script Date: 01/28/2013 13:33:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[trgUpdate] on [dbo].[Tbl_User_Information] 
for update
as
declare @EmpID as int;
declare @Field as varchar(50);
declare @Action as varchar(50);
 
select @EmpID=i.U_ID from inserted i;
if update(FullName)
	select @Field= (select c.name from syscolumns c join sysobjects o on o.id=c.id where c.name like '%FullName%');
set @Action='Updated';
 
insert into Test_Table values(@EmpID,@Field,@Action);
 
...the trigger compiled successfully but after an update on the the Tbl_user_information table for column named fullName....it returned an error saying the subquery returned multiple values. It's probably pointing to the select @Field=(select....) statement. i obtained the subquery from some link i got from google. It's not clear to me.
So how can i just insert the column name into another table (Test_Table) as specified in the code above? Any help will be apreciated.
Thanks in advance.
Minghang
Posted 27-Jan-13 22:02pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You select all fields that contain the "FullName" text - not only from a specific table, from all tables! So add an other condition to the where clause to restrict the query to the Tbl_User_Information table.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 277
1 Maciej Los 210
2 BillWoodruff 205
3 Jochen Arndt 180
4 DamithSL 165
0 OriginalGriff 5,130
1 DamithSL 4,157
2 Maciej Los 3,670
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,821


Advertise | Privacy | Mobile
Web04 | 2.8.141216.1 | Last Updated 28 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100