March 19, 2003 at 1:52 pm
Is there a way to add an extra column to an insert or update statement that isn't added to the db, but a trigger can see?
Ex: I have a table with 2 fields
ID int Identity
Val char(4)
I want to do an insert like this
Insert into table (Val,MyAddedField) Values('aaa',1)
this way I can see the MyAddedField in a trigger, but not as part of the table. I can't add fields to the table. I have to be able to do different things in the trigger depending on where the insert/update/delete comes from. Any ideas?
Thanks.
March 19, 2003 at 2:11 pm
This is impossible unless you actually create that column. Your solution would be to implement business logic in a stored procedure or elsewhere, not in the trigger.
March 19, 2003 at 2:15 pm
you can't insert a value in a field that doesn't exists. Maybe you can create a new table from the original table an create the trigger on that table. For example,
SELECT id, Val, 1 AS MyAddedField
INTO newTabla
FROM OriginalTable
March 20, 2003 at 4:34 am
Solution:
--Create a view that contains all columns from the base table.
CREATE VIEW InsteadView
-- Do the Cast so can specify the TYPE of what you want to 'insert'
AS SELECT Val, CAST('' AS VARCHAR(50)) as MyAddedField
FROM BaseTable
GO
--Create an INSTEAD OF INSERT trigger on the view.
CREATE TRIGGER InsteadTrigger on InsteadView
INSTEAD OF INSERT
AS
BEGIN
-- Do what you want here to use your different criterias in the MyAddedField.
-- Build an INSERT statement
INSERT INTO BaseTable (Val)
SELECT Val
-- REMEMBER that inserted table contains all the data you specified in your INSERT statement
FROM inserted
END
GO
Then do your inserts into the view.
Regards, H.Lindgren
Edited by - hanslindgren on 03/20/2003 05:11:11 AM
March 20, 2003 at 7:09 am
Thanks for all the replies. Unfortunately I don't have access to all the apps that write to the db so triggers are my only option. I can't make a view and use it from the apps. I need the trigger to fire or not depending on what app it comes from.
March 20, 2003 at 11:00 am
But if you don't have access to the Apps, how are you suppose to do your
quote:
Insert into table (Val,MyAddedField) Values('aaa',1)
Unless that your inserts already look like that but then I wonder what MyAddedField contains (or should contain)?
Are the apps doing inserts like you stated or are they doing Inserts without 'MyAddedField'?
If you can't change the apps and not change the table, what is it actually you want to achive?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply