Click here to Skip to main content
15,888,287 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hello experts, i want to be able to insert the name of the column that was modified into another table using after triggers.
SQL
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

1 solution

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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900