Column Name As Variable In Trigger
Jun 7, 2001
I am writing an audit trail trigger that needs to query columns, but the specific column names are not known until the trigger runs and determines what columns have changed. Once I know the changed column(s) I fetch the column name. No sweat.
The problem is using a variable in place of a column name I can not get the data to return, only the column name.
-- Here is how I get the column name. It works great, @CName
-- reflects the column name as I use it elsewhere fine.
Select @CName = col_name(OBJECT_ID('TABContract'), @CNum)
-- This simple syntax usually works, it does not using a
-- variable as the column name. @Prev is populated with
-- @CName literally. I have tried various and asundry ()'s and []'s
Select @Prev = @CName From Deleted
-- Here I have hard coded the Column name and ran a test modifying that column "Comment" and it works! @Prev is populated with the columns data not the Column name.
Select @Prev = Comment From Deleted
-- Tried these syntaxes, no luck
Select @Prev = (Select @CName From Deleted)
Select @Prev = (Select col_name(OBJECT_ID('TABContract'), @CNum) From Deleted)
Any help appreciated,
Ray
rwood@semcorp.com
View 2 Replies
ADVERTISEMENT
Sep 20, 2007
Hi all in .net I've created an application that allows creation of triggers, i also want to allow the deletion of triggers.
The trigger name is kept in a table, and apon deleting the record i want to use the field name to delete the trigger
I have the following Trigger
the error is at
DROP TRIGGER @DeleteTrigger
I'm guessing it dosen't like the trigger name being a variable instead of a static name
how do i get around this?
thanks in advance
-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER RemoveTriggers
ON tblTriggers
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @DeleteTrigger as nvarchar(max)
select @DeleteTrigger = TableName FROM DELETED
IF OBJECT_ID (@DeleteTrigger,'TR') IS NOT NULL
DROP TRIGGER @DeleteTrigger
GO
END
GO
View 7 Replies
View Related
Sep 9, 2004
Hello, I have a very simple trigger that only copies the data from one table to a historial table, when a row is deleted. It works fine, but now I need to change it becouse I am inserting a new column to the table, which type is ntext, and when I try to declare a ntext variable (inside the trigger), it does not allow me. Does anyone know how to go round this? Here is the code of the trigger (maybe it is not efficient, as I am a newbie in this :D, please tell if so as well)
CREATE TRIGGER OnDelete ON dbo.Tracker
FOR DELETE
AS
declare @IDregistro as integer;
declare @IDTIT as integer;
declare @negocio as nvarchar(50);
declare @fechaOr as datetime;
declare @asuntoOr as nvarchar(50);
declare @estadoOr as nvarchar(50);
declare @descripcionOr as nvarchar(255);
declare @usuarioOr as nvarchar(50);
declare @contactoOr as nvarchar(50);
declare @companiaOr as nvarchar(40);
--declare @desc as ntext; --DOES NOT WORK!!
declare @fechaSus as datetime
select @fechaSus = getdate()
select @IDregistro = IDregistro from deleted;
select @IDTIT = IDTitular from deleted;
select @negocio = negocio from deleted;
select @fechaOr = fecha from deleted;
select @asuntoOr = asunto from deleted;
select @estadoOr =estado from deleted;
select @descripcionOr = descripcion from deleted;
select @usuarioOr = usuario from deleted;
select @contactoOr = contacto from deleted;
select @companiaOr = compania from deleted;
insert into Historico (IDregistro, IDtitular, Negocio, FechaSuceso,TipoSuceso, FechaOriginal, AsuntoOriginal, EstadoOriginal, DescripcionOriginal, UsuarioOriginal, ContactoOriginal, CompaniaOriginal) Values (@IDregistro, @IDTIT, @negocio, @fechaSus, 'DELETE', @fechaOr, @asuntoOr, @estadoOr, @descripcionOr, @usuarioOr, @contactoOr, @companiaOr)
View 5 Replies
View Related
May 19, 2006
recently I wrote a TRIGGER , but I found the text is not allowed to be used.
such as:
in the trigger:
declare @content text
Is it illegal??? And why?
thank you!
View 7 Replies
View Related
Apr 30, 2008
Is it possible to get a variable( for example user id of the currently logged in user) in a CLR SQL Server trigger.
View 1 Replies
View Related
May 19, 2008
I need to pass a application userid on every call to my database for triggers on the db to use.
I am connecting to my database using integrated security from my application, all on one windows domain account. However I need to get an application user id over on the connection for triggers to use to stamp in changelogs.
Currently everytime we open a connection we create a #table with the user id in it, but that is expensive, especially in our new SOA where connections are frequently created and dropped on requests.
Is there any way I can get a connection level variable over to the database server so triggers could read it?
Any help is greatly appreciated.
View 1 Replies
View Related
Mar 7, 2006
Hi Guys,
i'm batttling with the below Trigger creation
__________________________________________________ _
CREATE TRIGGER dbo.Fochini_Insert ON dbo.FochiniTable AFTER INSERT AS
BEGIN
DECLARE @v_object_key VARCHAR(80)
DECLARE @v_object_name VARCHAR(40)
DECLARE @v_object_verb VARCHAR(40)
DECLARE @v_datetime DATETIME
SELECT ins.Cust_Id INTO @v_object_key FROM inserted ins <--- my problem area!!
SET @v_object_name = 'FochiniTable'
SET @v_object_verb = 'Create'
SET @v_datetime = GETDATE()
IF ( USER <> 'webuser' )
INSERT INTO dbo.xworlds_events (connector_id, object_key, object_name, object_verb, event_priority, event_time, event_status, event_comment)
VALUES ('Fochini', @v_object_key, @v_object_name, @v_object_verb, '1', @v_datetime,'0', 'Triggered by Customer CREATE')
END
________________________________________________
i'm trying to get the INSERTED variable from table FochiniTable on colomn Cust_Id
and the statement: SELECT ins.Cust_Id INTO @v_object_key FROM inserted ins - is failing [still a newbie on mssql server 2000]
any help will be appreciated
lehare.
View 1 Replies
View Related
Jan 23, 2008
Hi,
I have a trigger set on TABLE1 so that any update to this column should set off trigger to write to the AUDIT log table, it works fine otherwise but not the very first time when table1 has null in the column. if i comment out
and i.req_fname <> d.req_fname from the where clause then it works fine the first time too. Seems like null value of the column is messing things up
Any thoughts?
Here is my t-sql
Insert into dbo.AUDIT (audit_req, audit_new_value, audit_field, audit_user)
select i.req_guid, i.req_fname, 'req_fname', IsNull(i.req_last_update_user,@default_user) as username from inserted i, deleted d
where i.req_guid = d.req_guid
and i.req_fname <> d.req_fname
Thanks,
leo
View 7 Replies
View Related
Feb 23, 2006
I have a Table Name "Forums". I want to ceate an AFTER-Trigger on it. It will execute when ever a new row is inserted to "Froums" Table.
Here is what I did but It needs to be corrected:
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
ALTER TRIGGER CreateTopicsTableTrigger
ON dbo.Forums
AFTER INSERT
AS
SET NOCOUNT OFF
DECLARE @myNewForum varchar
CAST(@@ROWCOUNT as varchar) /*Is it OK???*/
SET @myNewForum=@myNewForum+@@ROWCOUNT /*Here I dont know how assigments work in SQL*/
GO
CREATE Table @myNewForum /*Will this work some how???*/
( TopicID int IDENTITY NOT NULL, TopicTitle varchar(50) , CreatedBy varchar(50) ,
DateCreated DateTime , DateLastUpdate DateTime , LastUpdateBy varchar(50) )
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
View 5 Replies
View Related
Nov 23, 2015
create table #t
(
id int,
col1 decimal(18,2)
)
go
[Code] ...
-- I want to subtract @X and col1. But my variable @X must be reduced for each value in col1 for each next row until it reaches zero.
-- OUTPUT:
-- id col1 col2
--@X at starting point is 15000
-- 1 5000.00 0 --@X IS 10000 = 15000 - 5000(col1)
-- 2 1000.00 0 --@X IS 9000 = 10000 - 1000
-- 3 10000.00 1000.00 --@X IS 1000 = 9000 - 10000
-- 4 12000.00 12000.00
-- 5 300.00 300.00
-- 6 35000.00 35000.00
--in col2 i just put zero where col1 is substract from @X and continue for every subsequent order.
-- in 3 row value is 1000 becouse @X is that big (1000 left from col1)
View 13 Replies
View Related
May 25, 2008
Hi, I want to use a variable and set it to column name but it just doesnt' workwhen I execute it. I got an error message "Invalid column name 'column1'. can you please help. ALTER PROCEDURE [dbo].[procedure_name] @var1 INT = NULLAS BEGIN IF(@var1 = '-1') BEGIN --SET @var1 = column1 END SELECT * FROM table1 WHERE column1 = @var1 Thank you
View 4 Replies
View Related
Feb 25, 2001
Using a stored Procedure, I want to select a record from a Table if a certain string appears in a specified column, so I use
CREATE PROCEDURE spSearcher
@String varChar(20)
as
Select Column1, Column2, Column3 from Table1
where Charindex(@String1,Column1) > 0
which works fine.
But the following doesn't work:
CREATE PROCEDURE spSearcher
@String varChar(20)
@ColumnName VarChar(20)
as
Select Column1, Column2, Column3 from Table1
where Charindex(@String,@ColumnName) > 0
How can I substitute a variable for Column1, so I can pass the name Column1 or Column2, or Column3, as desired?
Thanks in anticipation,
Peter Caspersonn.
View 1 Replies
View Related
Jun 2, 2004
I have a problem that I'm sure is very simple to answer for anyone that knows a bit of T-SQL. In a stored procedure, I simply want to concatenate a string variable containing a column name into a Select statement.
For example:
I want to execute the following statement but using a variable for the column name:
Select * from tblmet1araw where JulianDay = 1
JulianDay is an integer
This is how I have my code set up:
declare @xxx as varchar(20)
set @theday = 'JulianDay'
select * from tblmet1araw where @theday = 1
I get the following error:
Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value 'JulianDay' to a column of data type int.
View 2 Replies
View Related
Aug 11, 2006
Hi,
I have this query where I select data from the last 12 months (in 12 different columns).
What I would like to know is if it's possible to change the name of the columns to the month they refer to.
Thank's in advance,
AR
View 7 Replies
View Related
Jul 26, 2007
Is it possible to set a variable in a stored procedure equal to avalue from a column when that column's respective ID is equal tomax(id)-1ID A B1 24 242 53 293 76 474 32 32What I am trying to do is update A in the last column to be equal to Bfrom the 4th row, plus A from the 3rd row. If I could set a variableequal to A in row 3 (in this case 76) I could easily add the variableto the existing value.I've tried something like this before. I'm sure some of you will sayright away, "of coarse that doesn't work". I'm new to sql.Set A = A + select( B from mytable where ID = (select (max(ID)-1) frommytable))where ID = (select (max(ID)) from mytable)ThanksMatt
View 1 Replies
View Related
Sep 28, 2006
It is my second time on this forum
Nice people managed to help me on my first thread. I am encouraged to proceed with you !
Well I have an Excel source that returns One row , two columns: what I want is get the value returned in the first row - first colum into a variable so I can then execute sql tasks based on this filter parameter !
Can you guide me ?
Regards Jacques
View 5 Replies
View Related
Oct 1, 2006
Greetings,
I have a table that contains various columns in it totalling 12,000 rows of data. For example;
site_ref, account_title, gl_code, period1, period2, period3 etc through to period12
I wish to write a query that will allow me to search for specific site_ref, acount_title etc and then only one of the period columns. This period column will be specified by the user at the time of submitting the query through reporting services. How do I assign a column to a variable so that the user can set it in the report parameters and then the code will run against that specific column for the period?
Example would be to see everything for site_ref = 'tb', account_title = 'gross rent' and the financial figures within the column titled 'period10' or the next time they run the report they may wish to run it against the values in period7.
Any pointers would be appreciated
Regards
View 4 Replies
View Related
May 28, 2008
I have a Windows programme using VS STudio 2005 / C# /Windows XP and
Microsoft.Practices.EnterpriseLibrary.Data
I am trying to pass an 'in parameter' to a stored procedure which will be used in the sp as a column name . The column names in the database are numbers (in this case, 0 to 45). I wish to pass the column name from my Windows programme tot he sp a and use it in the sp as the variable @BallColumnValue. Can anyone please advise me of the correct way to do this?
Thanking you in anticipation.
lpbcorp
Windows programme along the lines of:
else if (subsetList == false)
{
db.AddInParameter(dbUpdateBallSuccess, "BallColumnValue", DbType.Int16, i);
db.AddInParameter(dbUpdateBallSuccess, "BallNo", DbType.Int32, 1);
}
db.ExecuteNonQuery(dbUpdateBallSuccess);
Stored procedure is as follows:
USE [MyDatabaseName]
GO
/****** Object: StoredProcedure [dbo].[MyTableNameInsertMainDrawBallRecency;] Script Date: 05/22/2008 22:12:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MyTableNameInsertMainDrawBallRecency;]
(
@BallColumnValue nvarchar(10),
@Recency int,
@date DateTime,
@weeksSinceDrawn int,
@priorWeeksSinceDrawn int
)
AS
DECLARE lastrow_cursor CURSOR SCROLL FOR
SELECT CAST(@BallColumnValue AS COLUMN) FROM MyTableNameRecencyMainDrawBall
OPEN lastrow_cursor
FETCH LAST FROM lastrow_cursor INTO @weeksSinceDrawn
FETCH PRIOR FROM lastrow_cursor INTO @priorWeeksSinceDrawn
SELECT @priorWeeksSinceDrawn
IF @Recency = 0
BEGIN UPDATE AuMondayLottoRecencyMainDrawBall
SET [@BallColumnValue] = @Recency
WHERE Date = @date
END
ELSE
UPDATE MyTableNameRecencyMainDrawBall
SET [@BallColumnValue] = @Recency + @priorWeeksSinceDrawn
WHERE Date = @date
CLOSE lastrow_cursor
DEALLOCATE lastrow_cursor
View 10 Replies
View Related
Oct 22, 2007
I have a table called Projects, within that table is a field called UserId
I want to log everytime that UserID field in changed in a log called ProjectAllocationLog
so far i have written
CREATE TRIGGER LogUser
ON Projects
FOR Insert, Update
AS
if Update(UserId)
begin
INSERT INTO "ProjectAllocationLog" ("projectid", "UserID", "createdate")
VALUES ("123", "123", Now())
End
What I am stuck with is how do I pass the values of the field into the Values
ie where I have put "123", I actually want to pass the value of ProjectID, and UserID from the projects row that is changing
can anyone help me please
View 5 Replies
View Related
Sep 22, 2006
Is it possible to use a column name variable in a Select Statement for a column name?For example I have a dropdown with FName,LName,Phone and a text box. The user can select the field to search and the criteria will go into the text box. The problem is the select doesn't like a variable for the field name. I have tried both a standard variable and a Case statement (see below). This is being used in a Stored Procedure with MSSQL. The actual select is much more complicated than this but it gets the point across. Thanks for your help in advance@Field as varchar( 50),@Value as varchar (50)SELECT *FROM customersWHERE @Field = @ValueORSELECT *FROM customersWHERE CASE WHEN @Field = 'Fname' THEN Fname = @Value END, CASE WHEN @Field = 'Lname' THEN Lname = @Value END, CASE WHEN @Field = 'Phone' THEN Phone = @Value END;
View 1 Replies
View Related
Jan 20, 2000
I need to be able to pass a parameter to a stored procedure indicating which column to sort the outcome by. I cannot simply sort it by the passed variable (or I have the syntax wrong...). The sort can be anyone of eight columns and I need to do this in a fair few places on complex SELECT statements, so I am reluctant to use a case statement, which would make the sp rather large.
View 1 Replies
View Related
Jul 30, 1999
I have assigned the name of columns to a variable (@colum_name):
set @col_name = (select name from syscolumns
where id = object_id('table_name') and colid = @counter
The value of @col_name changes as the counter iterates through the different columns. The PROBLEM IS RUNNING THE FOLLOWING QUERY:
select @col_name from table_name where 'primary key = xyz'
The query returns the value of the variable @col_name. I want the value of the column in the table with name = @col_name.
I am new to T-SQL so please feel free to point out any mistakes.
Any suggestions would be greatly appreciated.
Thanks in advance.
Shaleen
View 1 Replies
View Related
Jul 21, 2004
Hi ,
Can anyone guide me to resolve my problem . I need to write a procedure which first looks for the Worker names from WORKER table who satisfies certain criterias , and then Find from another table how many jobs each one has done on each day of a month . I have written a function which will return all days of a particular month, which can be used for the above procedure .If the wrokers are John , Alex and Martin ,( which may vary according to the branch parameter) The report should look like
Day John Alex Martin
1/5/2004 4 8 NULL
2/5/2004 5 9 12
------------------------
etc
Thanks in advance
Regards
Praveen ( praveenvc@rediffmail.com)
View 2 Replies
View Related
Jan 20, 2004
OK.. I've got a stored procedure I'm writing, which accepts an argument called @statfield... let's say I want to use this variable as a literal part of a SQL statement, example:
select * from table1 where @statfield = @value
I want to do basically an eval(@statfield) so if @statfield is "key_id", then the select statement comes out:
select * from table1 where key_id = @value
How can I do this?
Thanks!
View 5 Replies
View Related
Mar 15, 2004
I am writing a stored procedure in which I have a query that selects the Headings of Columns from another table...
I want to then create a loop that will contain a variable with the value of the column heading and then set the column to a value of NULL...
Is there any way to accomplish this???
I thought about placing these values into a temp table...
This is what I have so far...
Declare @QueryX nvarchar(500)
Declare @FieldName varchar(20)
Create Table #UpdateRejDoc
(
Abbreviation varchar(20)
)
Insert into #UpdateRejDoc
Select Abbreviation
From tbl_Titles
left JOIN tbl_TitleRouting on tbl_Titles.Title_ID = tbl_TitleRouting.Title_ID
Where tbl_TitleRouting.Application_ID = @Application_ID
While Exists (Select Abbreviation from #UpdateRejDoc)
Begin
Set @QueryX = 'Update DBLandfillUser.tbl_ObjectApprovals' +
'@AppName + Set @FieldName = null Where object_id =' + Cast(@object_id as VarChar(20))
End
View 1 Replies
View Related
Mar 5, 2014
how to get multiple column value to variable in PDW/DSQL?such as below
declare @a table(col1 int, col2 int)
insert into @a values (1,2)
declare @c int, @d int
select @c = col1, @d = col2
from @a
select @c, @d
View 3 Replies
View Related
Jan 9, 2008
Hi there
I am migrating all my DTSs to SSISs. One of them is a very basic DTS, that copies data from a text file and places it in a table with one extra column. That extra column is populated with the value of a global variable.
In DTS mode I was creating a custom ActiveX transformation stating
DTSDestination("FieldName")=DTSGlobalVariables("VariableName").Value
Can any one advise me on the best way to accomplish this in the SSIS?
I tried several approaches but all failing.
Many thanks
ds9
View 3 Replies
View Related
Sep 8, 2006
Hi,
i'm working on a Data Flow which uses a "Flat Sile Source" to read a CSV-file and then sends the transformed data to a "OLE DB Destination".
What i need is a way to add a column to my transformed data which contains a value from a User-Variable.
My User-Variable contains the key for the data, and this one value shall replicate to all Rows in the DataSet.
So anybody know of an existing Data Flow component, which can do this?
Regards, Martin
View 3 Replies
View Related
Sep 29, 2007
Code Block
ALTER TABLE @OrderPrice ADD(description varchar(50) );
What's wrong?
View 3 Replies
View Related
Aug 23, 2006
I have a For Each Loop that iterates over a recordset stored in a variable. One of the columns in the recordset is type xml and I want to map it to a variable using Variable Mappings of the For Each Loop container. I am getting this error:
Error: 0xC001C012 at FELC Loop thru report defs: ForEach Variable Mapping number 4 to variable "User::Parameters_xml" cannot be applied.
I have tried changing the type of the Parameters_xml variable to Object and String, but I get the same error. Any ideas?
View 3 Replies
View Related
Mar 25, 2008
Hi,
I would like to assign the value of a variable to a column name of a table. For eg:
declare @sam varchar(100)
set @sam = 'Insert'
create table #temp(Sample varchar(100) not null)
insert into #temp(Sample) Values(@sam)
drop table #temp
I would like to generate a table whose column name should be generated dynamically. The value of @sam that is "Insert "should be set to the column name. Is there a way to assign like this. Can anyone please help me in achieving this.
Thanks,
Sana.
View 1 Replies
View Related
Oct 1, 2007
Hi,
I am transferring data from msaccess to sql2005 using foreach loop. I have a table called Students and a column in it called State.
I want to store the value of the State in a variable in the foreach loop. I am using Dataflowtask to transfer rows from access to sql. what component can i use inside dft to store the value of State in a variable.
Plz its urgent
thanku.
View 5 Replies
View Related
Oct 1, 2007
is this possible??
DECLARE @fr varchar(50)
SET @fr = (Select ActivityText FROM Activity WHERE ActivityID =1)
SELECT ContactRef AS @fr FROM Contact WHERE ContactID =1
etc
Cheers,
Craig
View 2 Replies
View Related
