January 30, 2012 at 12:07 am
Hi Guys, I'm new to SQL and i need your help.
Scenario: I got a Table named ORDER_LINE and it has plenty of columns. One of them are CBM1 and CBM2. CBM1 is linked to a software and I cannot touch the settings and I have created CBM2 for my use. I want to make a trigger that will copy every new, deleted and updated data to CBM2 column.
How am i going to do this?
Thanks in advance.
January 30, 2012 at 1:28 am
jethrojet (1/30/2012)
I got a Table named ORDER_LINE and it has plenty of columns. One of them are CBM1 and CBM2. CBM1 is linked to a software and I cannot touch the settings and I have created CBM2 for my use. I want to make a trigger that will copy every new, deleted and updated data to CBM2 column.
The following example demonstrates adding a new CBM2 column and copying CBM1 values to the CBM2 column for new rows:
USE tempdb;
GO
-- Example table
CREATE TABLE dbo.OrderLine
(
OrderId integer NOT NULL,
OrderLine integer NOT NULL,
CBM1 integer NOT NULL,
PRIMARY KEY (OrderId, OrderLine)
);
GO
-- Sample existing data
INSERT dbo.OrderLine
(OrderId, OrderLine, CBM1)
VALUES
(1, 1, 165),
(1, 2, 241),
(1, 3, 645),
(2, 1, 356),
(3, 1, 863),
(3, 2, 384),
(3, 3, 184),
(3, 4, 845);
GO
-- Add the new column and trigger in a transaction
BEGIN TRANSACTION;
-- Add column
ALTER TABLE dbo.OrderLine ADD
CBM2 integer NULL;
GO
-- Add trigger
CREATE TRIGGER
ON dbo.OrderLine
INSTEAD OF INSERT
AS
BEGIN
INSERT dbo.OrderLine
(OrderId, OrderLine, CBM1, CBM2)
SELECT
i.OrderId,
i.OrderLine,
i.CBM1,
i.CBM1
FROM INSERTED AS i;
END;
GO
COMMIT TRANSACTION;
GO
-- Set CBM2 = CBM1 for existing records
UPDATE dbo.OrderLine
SET CBM2 = CBM1;
GO
SELECT * FROM dbo.OrderLine AS ol
GO
-- Add a new order line
INSERT dbo.OrderLine
(OrderId, OrderLine, CBM1)
VALUES
(4, 1, 552);
GO
-- Show CBM2 has been populated
SELECT * FROM dbo.OrderLine AS ol;
GO
-- Can update the CBM2 columns
UPDATE dbo.OrderLine
SET CBM2 = 100
WHERE
OrderId = 4
AND OrderLine = 1;
GO
-- Final look at the table
SELECT * FROM dbo.OrderLine AS ol;
GO
-- Tidy up
DROP TABLE dbo.OrderLine;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 30, 2012 at 10:05 am
A couple of things.
First, Paul, why an INSTEAD OF TRIGGER? I'm assuming that will have some performance benefit over an AFTER trigger.
Secondly, since the CBM2 column is one that you added to the table, why not use a computed column. You'd just need to do this:
ALTER TABLE order_line
ADD CBM2 AS CBM1
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 30, 2012 at 7:16 pm
Jack Corbett (1/30/2012)
First, Paul, why an INSTEAD OF TRIGGER? I'm assuming that will have some performance benefit over an AFTER trigger.
That's one of the reasons, yes.
Secondly, since the CBM2 column is one that you added to the table, why not use a computed column.
I was assuming the CBM2 column would need to be writable, after being initialized with the same value as CBM1.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 31, 2012 at 3:36 pm
I mentioned the computed column because I wasn't sure if it needed to be writable.
I'll have to play around a bit with the sample code to see how the INSTEAD OF compares to and differs from an AFTER trigger.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 31, 2012 at 3:48 pm
Jack Corbett (1/31/2012)
I mentioned the computed column because I wasn't sure if it needed to be writable.I'll have to play around a bit with the sample code to see how the INSTEAD OF compares to and differs from an AFTER trigger.
Double Update, Jack. INSERT comes in with blank CBM2. AFTER Trigger Fires, firing an UPDATE event on the dirty record the INSERT has already dropped off. Double write. using INSTEAD OF, you do a single INSERT event, and don't accidentally fire off an UPDATE trigger as well.
You usually wouldn't notice because most triggers aren't self-affecting.
On that note, however, Jethro, what in particular are you trying to translate the CBM1 data from/to in CBM2? I agree in general a calculated column would probably be less painful, if it can be made persisted, which means it has to be a hard formula. If you describe a few examples we can probably help you find that formula.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 31, 2012 at 10:38 pm
Thanks for the reply m8s.
I'll try everything you have said.
I'll be back...
Thanks
February 3, 2012 at 12:57 am
I have figured out that my trigger is not working because CBM2 in a Calculated Column.
Correct me if i'm wrong guys...
So, what i did is, I eliminated CBM2 and created a trigger with calculation on CBM1 and it worked.
CREATE TRIGGER [dbo].[linecbm] ON [dbo].[ORDER_LINE]
AFTER UPDATE
AS
IF UPDATE (CBM1)
UPDATE sl
SET sl.CBM1 = i.CBM * i.ORDER_QTY
FROM ORDER_LINE AS sl
INNER JOIN inserted AS i ON i.RECORD_IDENTITY = mt.RECORD_IDENTITY
Thanks everyone
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply