Using Trigger with dynamic column name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using Trigger with dynamic column name

Author

Topic

June
Starting Member

18 Posts

Posted - 2009-01-29 : 01:36:51

Hi,
I am developing a Material management system where I have an Amendment table. It contains the amendments of Terms & Conditions for an order.

Amendment

OrderNo IRN VendorID Description AmdFor AmdRead AmdDate

Here IRN is the indent no, Description is the Term which is changed, AmdFor is the Term value befor change and AmdRead is the Term value after change. I have another table CSTerms which contains the original Terms & Conditions.

CSTerms

IRN CSDate VendorID Validity DeliveryPeriod PayTerms Tax ExciseDuty Discount Others

I want to set a Trigger in Amendment table which will update the CSTerms table on insertion of a new row in Amendment. But my problem is that Description could be any of the columns of CSTerms and so the trigger in Amendment should update only that column in CSTerms. How can I create such a Trigger that will update CSTerms dynamically. Thanks for any help.

June
Starting Member

18 Posts

Posted - 2009-01-30 : 02:48:58

where are all the fabled MVP's?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-30 : 03:06:55

Seems like nobody understands your problem.
Maybe you can elaborate using sample data?

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

June
Starting Member

18 Posts

Posted - 2009-01-30 : 05:46:36

quote:
Originally posted by webfred

Maybe you can elaborate using sample data?

Webfred

CSTerms

IRN CSDate VendorID Validity DeliveryPeriod

M3015 11-01-2009 8 60 days 12 week

Now If Validity is changed,say from 60 Days to 40 Days, then a row will be inserted to the Amendment table.

Amendment

IRN VendorID Description AmdFor AmdRead
M3015 8 Validity 60 Days 40 Days

Description contains the column that has been modified. AmdFor contains the value before modification and AmdRead contains the value after modification.

Now I want to set a Trigger in Amendment table which will update the CSTerms table on insertion of a row in Amendment. The trigger in Amendment should update only that column in CSTerms(here Validity) which is modified and from the original value to the modified value(here from 60 Days to 40 Days).
Hope this is enough...lol

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-01-30 : 06:18:56

I suspect your schema could be improved.

Something like the following should work:

UPDATE C
SET VendorId =
        CASE
            WHEN I.[Description] = 'VendorId'
            THEN I.AmdRead
            ELSE C.VendorId
        END
    ,Validity =
        CASE
            WHEN I.[Description] = 'Validity'
            THEN I.AmdRead
            ELSE C.Validity
        END
    ,DeliveryPeriod
        CASE
            WHEN I.[Description] = 'DeliveryPeriod'
            THEN I.AmdRead
            ELSE C.DeliveryPeriod
        END
FROM CSTerms C
    JOIN inserted I
        ON C.IRN = I.IRN

June
Starting Member

18 Posts

Posted - 2009-02-02 : 00:36:54

Thanks. This is what I have done..

CREATE TRIGGER t_Amendment
ON Amendment
FOR INSERT
AS
DECLARE @stringval varchar(30)
select @stringval = inserted.[Description]
FROM inserted

UPDATE CSTerms
SET @stringval=inserted.AmdRead

FROM CSTerms
JOIN inserted
ON CSTerms.IRN = inserted.IRN and CSTerms.VendorID = inserted.VendorID

But it is not updating the CSTerms table. I think the problem lies in SET @stringval.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 00:41:18

shouldnt it be?

UPDATE CSTerms
SET inserted.AmdRead=@stringval

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-02 : 00:42:30

Using @stringval doesn't make any sense. Could you explain what your intention is with that variable?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 01:30:45

Yup. As Tara suggests, this will only work as expected when you do a single row insert. when more than one row is inserted, the inserted table will contain more than one row and first select will cause only one of row's description value to be store in variable. so probably what you need is table variable.

June
Starting Member

18 Posts

Posted - 2009-02-02 : 06:26:45

quote:
Originally posted by visakh16

UPDATE CSTerms
SET inserted.AmdRead=@stringval

When I tried it I got the error "cannot use column prefix inserted".

quote:
Using @stringval doesn't make any sense

I know. I used it only to show that nothing is working as intented. I think I will have to go the dynamic way.thanks for your time Tara and visakh.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-02-02 : 07:53:20

quote:
Originally posted by visakh16

shouldnt it be?

UPDATE CSTerms
SET inserted.AmdRead=@stringval

This makes no sense.
You cannot update any column in table INSERTED.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 09:16:36

didnt notice inserted. i thought you're updating CSTerms column value. but reading it again i think what you need is dynamic sql. why is your column name stored as a value in amendment? also will you be sure of values that can come inside it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 09:32:13

ok. Here's one method


CREATE TRIGGER t_Amendment 
ON Amendment
FOR INSERT
AS

UPDATE c
SET c.CSDate=COALESCE(i.CSDate,c.CSDate),
c.Validity=COALESCE(i.Validity,c.Validity),
c.DeliveryPeriod=COALESCE(i.DeliveryPeriod,c.DeliveryPeriod) 
FROM CSTerms c
JOIN 
(SELECT VendorID,IRN,
MAX(CASE WHEN [Description]='CSDate' THEN AmdRead ELSE NULL END) AS CSDate,
MAX(CASE WHEN [Description]='Validity' THEN AmdRead ELSE NULL END) AS Validity,
MAX(CASE WHEN [Description]='DeliveryPeriod' THEN AmdRead ELSE NULL END) AS DeliveryPeriod  
FROM inserted
GROUP BY VendorID,IRN)i
ON c.IRN = i.IRN 
and c.VendorID = i.VendorID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources