Click here to Skip to main content
12,068,967 members (65,599 online)
Rate this:
 
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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web04 | 2.8.160208.1 | Last Updated 28 Jan 2013
Copyright © CodeProject, 1999-2016
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