February 10, 2015 at 12:41 pm
Hi,
I was wondering if there was a way to redirect an insert to another column...
Example:
Original Insert Statement:
INSERT INTO
([columnA], [columnB])
SELECT '2015-01-01 00:00:00', 99.99
We have changed [columnB] from a decimal(19,9) to a computed column. So instead, we added another column [ColumnC] to take [ColumnB]'s insert data. I thought we could've used a trigger instead of insert, but that fails with the message "cannot be modified because it is either a computed column or is the result of a UNION operator".
This is the trigger I was using:
CREATE TRIGGER [Trigger] ON
INSTEAD OF INSERT
AS
INSERT INTO
([columnA], [columnC])
SELECT [dataA], [dataB]
FROM Inserted
Thank you.
February 10, 2015 at 1:09 pm
dajonx (2/10/2015)
Hi,I was wondering if there was a way to redirect an insert to another column...
Example:
Original Insert Statement:
INSERT INTO
([columnA], [columnB])
SELECT '2015-01-01 00:00:00', 99.99
We have changed [columnB] from a decimal(19,9) to a computed column. So instead, we added another column [ColumnC] to take [ColumnB]'s insert data. I thought we could've used a trigger instead of insert, but that fails with the message "cannot be modified because it is either a computed column or is the result of a UNION operator".
This is the trigger I was using:
CREATE TRIGGER [Trigger] ON
INSTEAD OF INSERT
AS
INSERT INTO
([columnA], [columnC])
SELECT [dataA], [dataB]
FROM Inserted
Thank you.
You can't insert into a computed column. This check happens prior to the trigger firing. The actual insert never happens because the statement is unable to be parsed. Could you make columnC the computed column instead?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 10, 2015 at 2:11 pm
Thank you for your response.
That's what I thought (the checks occur before the trigger firing), but I couldn't find that info in writing anywhere. Unfortunately, the [ColumnB] is used everywhere so that's why they don't want to give the column another name.
February 10, 2015 at 2:29 pm
dajonx (2/10/2015)
Thank you for your response.That's what I thought (the checks occur before the trigger firing), but I couldn't find that info in writing anywhere. Unfortunately, the [ColumnB] is used everywhere so that's why they don't want to give the column another name.
Another option might be to rename the original table to something else, then create a view that pulls ColumnA, ColumnC as ColumnB. It is a hack but might work until you can fix it correctly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 10, 2015 at 5:10 pm
Here's one way but whether you want to use it or not depends on what else is going on with that table.
CREATE TABLE dbo.Test
(
columnA DATETIME
,columnB DECIMAL(19,9)
);
GO
-- Insert works
INSERT INTO dbo.Test ([columnA], [columnB])
SELECT '2015-01-01 00:00:00', 99.99;
GO
-- Change columnB to computed
ALTER TABLE dbo.TEST ADD columnC DECIMAL(19,9);
GO
UPDATE a
SET columnC = columnB
FROM dbo.Test a;
GO
ALTER TABLE dbo.Test DROP COLUMN columnB;
GO
ALTER TABLE dbo.Test ADD columnB AS (columnC);
GO
-- Now the same insert fails
INSERT INTO dbo.Test ([columnA], [columnB])
SELECT '2015-01-01 00:00:00', 99.99;
GO
-- The column "columnB" cannot be modified because it is either a computed column or is the result of a UNION operator.
-- Run once
SELECT columnA, columnC
INTO dbo.Test_Mirror -- Create a new table
FROM dbo.Test;
-- And add the computed column you want
ALTER TABLE dbo.Test_Mirror ADD columnB AS (columnC);
GO
DROP TABLE dbo.Test;
GO
-- Substitute VIEW for the original table
CREATE VIEW dbo.Test
AS
SELECT [columnA], [columnB]=columnC
FROM dbo.Test_Mirror
GO
-- Now the same insert works again (inserts thru the new VIEW)
INSERT INTO dbo.Test ([columnA], [columnB])
SELECT '2015-01-02 00:00:00', 99.99;
SELECT *
FROM dbo.Test_Mirror;
GO
DROP TABLE dbo.Test_Mirror;
DROP VIEW dbo.Test;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply