February 24, 2012 at 1:15 am
Hi;
This is my table
Col1 Col2
A B
bb CC
In end user update the columns col1, col 2 I want to maintain the updated record and new insert record
and one help this.
Expected output
Oldval newvalue columns
A M Col1
bb C Col1
B ts Col2
any one help this ..
Thanks
February 24, 2012 at 1:28 am
what you asking for is a trigger
take a look at triggers and the before update statement, you can then have an audit table
so for example i have the below table with the row of data
table1
col1, col2, col3
1, def, ghi
i then do
update table1 set col1 = 'abc'
you could do in the trigger
insert into table2 (col1, col2, col3, datestamp) select col1, col2, col3, getdate() from inserted
that way you have the original record in table2 with the updated record in table1
so table 1 will show, 1, abc, ghi and table 2 will show 1,def,ghi,2012-02-24 00:00:00.000
February 24, 2012 at 1:32 am
Hi;
could you please tell me another method T-SQL
Using T-SQL to solve this problem.
February 24, 2012 at 1:39 am
triggers are t-sql
you will need to provide sample data, DML and expected results in a consumable format so we can help you out and explan clearly what you need, please see the first link in my signature
February 24, 2012 at 2:07 am
I suppose you could use the OUTPUT clause too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2012 at 2:11 am
Didnt know about the output clause, thanks for that on Gail.
February 24, 2012 at 2:52 am
HI
Thanks for response
Here my column names are dynamically passed
Example
create proc test(@table_name varchar(max),@value varchar(max)
as
SET @sql='UPDATE '+@table_name+' SET '+@value +' WHERE CD='+convert(VARCHAR(50),@cd)
EXEC SP_EXECUTESQL @sql
Column names
passed in the @valueparameter in stored procedure
like an @value='COUMNNAME=''TEST'', COUMNNAME=''TEST'', COUMNNAME=''TEST'''
@table_name= tablename
In this case How can apply the output clause in dynamic update query
any ony help ...
Here expected output the updated column value stored in temp table
oldvalue newvalue columnname table format
February 24, 2012 at 3:12 am
If I understand correctly, this should do:
-- CREATE A TABLE FOR TESTING
CREATE TABLE testTab (
cd varchar(max),
someColumn varchar(max)
)
GO
-- INSERT SOME TEST VALUES
INSERT INTO testTab VALUES ('some value','initial value')
GO
-- CREATE THE PROCEDURE
CREATE PROC testOutput
@table_name varchar(max),
@value varchar(max),
@cd varchar(max)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
-- BUILD THE DYNAMIC SQL WITH OUTPUT CLAUSE
SET @sql =
' UPDATE ' + @table_name +
' SET someColumn = @value ' +
' OUTPUT DELETED.someColumn, INSERTED.someColumn ' +
' WHERE CD = @cd '
-- USE A TABLE VARIABLE TO CAPTURE THE OUTPUT
DECLARE @updatedRows TABLE (
oldValue varchar(max),
newValue varchar(max)
)
-- EXECUTE THE DYNAMIC SQL AND PIPE THE OUTPUT TO THE TABLE VARIABLE
INSERT @updatedRows
EXEC SP_EXECUTESQL @sql, N'@value varchar(max), @cd varchar(50)', @value, @cd
-- SELECT OUT THE BEFORE/AFTER VALUES
SELECT *
FROM @updatedRows
END
GO
-- RUN THE PROCEDURE
EXEC testOutput
@table_name = 'testTab',
@value = 'new value',
@cd = 'some value'
-- Gianluca Sartori
February 24, 2012 at 4:51 am
Gianluca Sartori (2/24/2012)
If I understand correctly, this should do:
-- CREATE A TABLE FOR TESTING
CREATE TABLE testTab (
cd varchar(max),
someColumn varchar(max)
)
GO
-- INSERT SOME TEST VALUES
INSERT INTO testTab VALUES ('some value','initial value')
GO
-- CREATE THE PROCEDURE
CREATE PROC testOutput
@table_name varchar(max),
@value varchar(max),
@cd varchar(max)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
-- BUILD THE DYNAMIC SQL WITH OUTPUT CLAUSE
SET @sql =
' UPDATE ' + @table_name +
' SET someColumn = @value ' +
' OUTPUT DELETED.someColumn, INSERTED.someColumn ' +
' WHERE CD = @cd '
-- USE A TABLE VARIABLE TO CAPTURE THE OUTPUT
DECLARE @updatedRows TABLE (
oldValue varchar(max),
newValue varchar(max)
)
-- EXECUTE THE DYNAMIC SQL AND PIPE THE OUTPUT TO THE TABLE VARIABLE
INSERT @updatedRows
EXEC SP_EXECUTESQL @sql, N'@value varchar(max), @cd varchar(50)', @value, @cd
-- SELECT OUT THE BEFORE/AFTER VALUES
SELECT *
FROM @updatedRows
END
GO
-- RUN THE PROCEDURE
EXEC testOutput
@table_name = 'testTab',
@value = 'new value',
@cd = 'some value'
This is the route I would have taken also. If you wanted to physically store the inserted/deleted values you would insert the @updatedrows into an actual table.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply