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 406
1 /\jmot 180
2 Suraj Sahoo | Coding Passion 170
3 Afzaal Ahmad Zeeshan 159
4 BillWoodruff 154
0 OriginalGriff 8,344
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,634
3 Maciej Los 5,024
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.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