using a varible in an insert operation

Question

Post reply

using a varible in an insert operation

veloLewis

SSC Enthusiast

Points: 194
More actions
January 30, 2003 at 7:18 pm

#80681

I have an insert statement and have a need to use a local varible like so ins.@myvariable). Is there a way in T-SQL to do this. Below is the complete statement taken from an insert trigger. (ins.@cname is the problem)
example:
insert into auditHistory
(auditOrgId, auditPropID, auditTableName, auditTableColumn, auditColumnType, auditAction, auditData)
select 'IGT', 'IGT', 'ImageTypes', @cName, @cType, 'I', ins.@cName
from inserted ins

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

Are you sure this the complete trigger statement?

What sets the @cName and @cType variables?

Thanks

Phill Carter

--------------------
Colt 45 - the original point and click interface

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 2

BOL: Variable names must begin with an at sign (@). Local variable names must conform to the rules for identifiers. For more information, see Using Identifiers.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 3

5409045121009-7368

SSCoach

Points: 15740

January 30, 2003 at 9:08 pm

#447683

Where is ins.@cName defined / supplied from?

veloLewis SSC Enthusiast Points: 194 More actions · Answer 4

This is the trigger as it exists today. What I would like to do is only have one insert/select statement, therefore eliminating the if/else and be able to populate the data from the inserted table based upon the column being processed during the fetch statement.

ALTER TRIGGER [itr_imageTypes] ON [dbo].[ImageTypes]

FOR INSERT

AS

declare @cName sysname

declare @cType sysname

declare bCursor insensitive cursor

for

SELECT B.COLUMN_NAME, B.DATA_TYPE

FROM INFORMATION_SCHEMA.TABLES A, INFORMATION_SCHEMA.COLUMNS B

WHERE A.TABLE_NAME = B.TABLE_NAME and a.table_name = 'imageTypes'

for read only

open bCursor

fetch next from bCursor into @cName, @cType

while @@fetch_status = 0

begin

if @cName = 'companynbr'

insert into auditHistory

(auditOrgId, auditPropID, auditTableName, auditTableColumn, auditColumnType, auditAction, auditData)

select 'IGT', 'IGT', 'ImageTypes', @cName, @cType, 'I', ins.CompanyNbr

from inserted ins

else

if @cName = 'ImageType'

insert into auditHistory

(auditOrgId, auditPropID, auditTableName, auditTableColumn, auditColumnType, auditAction, auditData)

select 'IGT', 'IGT', 'ImageTypes', @cName, @cType, 'I', ins.ImageType

from inserted ins

fetch next from bCursor into @cName, @cType

end

close bCursor

deallocate bCursor

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

The insert statement might look something like this -

insert into auditHistory

(auditOrgId, auditPropID, auditTableName, auditTableColumn, auditColumnType, auditAction, auditData)

select 'IGT', 'IGT', 'ImageTypes', @cName, @cType, 'I', ins.@cName

from inserted ins

I have not been able to get the ins.@cName to actually reference the data in the local variable column name that is stored in @cName.

Santosh Nikam SSC Rookie Points: 40 More actions · Answer 5

Hi,

Please try this -

insert into auditHistory

(auditOrgId, auditPropID, auditTableName, auditTableColumn, auditColumnType, auditAction, auditData)

SELECT 'IGT', 'IGT', 'ImageTypes', B.COLUMN_NAME, B.DATA_TYPE, 'I',

CASE WHEN B.COLUMN_NAME = 'CompanyNbr' THEN CONVERT(NVARCHAR,I.CompanyNbr)

WHEN B.COLUMN_NAME = 'ImageType' THEN CONVERT(NVARCHAR,I.ImageType)

END AS S

FROM INFORMATION_SCHEMA.TABLES A, INFORMATION_SCHEMA.COLUMNS B, Inserted I

WHERE A.TABLE_NAME = B.TABLE_NAME and A.TABLE_NAME = 'ImageTypes' AND

(B.COLUMN_NAME = 'CompanyNbr' OR B.COLUMN_NAME = 'ImageType')

Hope this helps you,

Regards,

Santosh.