Click here to Skip to main content
11,802,389 members (30,170 online)
Rate this: bad
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]
/****** Object:  Trigger [dbo].[trgUpdate]    Script Date: 01/28/2013 13:33:47 ******/
ALTER trigger [dbo].[trgUpdate] on [dbo].[Tbl_User_Information] 
for update
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 from syscolumns c join sysobjects o on where 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 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.
Posted 27-Jan-13 21:02pm

1 solution

Rate this: bad
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.

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

  Print Answers RSS
0 OriginalGriff 475
1 KrunalRohit 366
2 F-ES Sitecore 295
3 Afzaal Ahmad Zeeshan 230
4 CHill60 220
0 OriginalGriff 2,950
1 Maciej Los 1,910
2 KrunalRohit 1,862
3 CPallini 1,645
4 Richard MacCutchan 1,157

Advertise | Privacy | Mobile
Web03 | 2.8.151002.1 | Last Updated 28 Jan 2013
Copyright © CodeProject, 1999-2015
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