January 29, 2009 at 10:40 pm
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.
regards,
Rajkhowa
January 30, 2009 at 5:13 am
CREATE TABLE Amendment (idcol int identity, descript varchar(30), value varchar(30))
go
CREATE TABLE CSTerms (idcol int identity, col1 varchar(30), col2 varchar(30), col3 varchar(30))
go
INSERT INTO CSTerms values ('', '','')
go
CREATE TRIGGER t_Amendment
ON Amendment
FOR INSERT, UPDATE
AS
DECLARE @stringval varchar(30),
@value varchar(30),
-- @id int,
@totalstring varchar(2000)
select @stringval = inserted.descript,
@value = inserted.value
-- @id = inserted.idcol
FROM inserted
SET @totalstring = 'update CSTerms set '+@stringval+' = '''+@value+''' '
EXEC(@totalstring)
GO
--insert - (bb is the name of the column, successfull is the value)
insert into Amendment values('col1', 'successfull')
SELECT * FROM CSTerms
DROP TABLE Amendment
DROP TABLE CSTerms
go
February 1, 2009 at 10:34 pm
Thanks Telammica. 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.
February 1, 2009 at 11:40 pm
Of course it will not work. You are not updating the table, you care updating a variable
UPDATE CSTerms
SET @stringval=inserted.AmdRead
FROM CSTerms
JOIN inserted
ON CSTerms.IRN = inserted.IRN and CSTerms.VendorID = inserted.VendorID
If you want it to work dynamically - you will have to build the sql string then execute it.
Follow the example like I did - and it should work:
SET @totalstring = 'update CSTerms set '+@stringval+' = '''+@value+''' '
EXEC(@totalstring)
in this case @value was a string value which I assigned to a variable. Just use @totalstring to build your update string - then use EXEC to execute it
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply