February 16, 2012 at 8:16 pm
Hi guys, I'm still studying on triggers. I have learn a lot from SSC :satisfied: thanks
I have a small problem regarding on triggers. I want to auto copy some data from Table #1(Master) to my working table (Orders) when I encode the PART_ID.
DB and Tables:
CREATE DATABASE TEST01
USE TEST01
CREATE TABLE MASTERS
(PART_ID CHAR(6),
CODE CHAR(6),
SIZE NUMERIC(10,2),
ITEM_DESCRIPTION CHAR(50))
CREATE TABLE ORDERS
(ORDER_ID CHAR(6),
PART_ID CHAR(6),
QUANTITY NUMERIC(10,0),
CODE CHAR(6),
SIZE NUMERIC(10,2))
Data:
INSERT INTO MASTERS
VALUES ('1000','A100','10','TYPE A')
INSERT INTO MASTERS
VALUES ('1001','A101','12','TYPE A')
INSERT INTO MASTERS
VALUES ('1002','A200','20','TYPE B')
INSERT INTO MASTERS
VALUES ('1003','A210','22','TYPE B')
INSERT INTO MASTERS
VALUES ('1004','A235','30','TYPE C')
INSERT INTO MASTERS
VALUES ('1005','A525','50','TYPE D')
INSERT INTO MASTERS
VALUES ('1010','B100','90','TYPE G')
INSERT INTO MASTERS
VALUES ('1011','A550','100','TYPE G')
If I'll go to the ORDERS table and insert some values based on PART_ID, I want the trigger to fetch some available data from the MASTERS; CODE and SIZE.
THanks
February 16, 2012 at 9:11 pm
How about something like this:
CREATE TRIGGER dbo.ORDERS_tg
ON dbo.ORDERS
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE o
SET CODE = m.CODE
,SIZE = m.SIZE
FROM ORDERS o
INNER JOIN Inserted i
ON i.ORDER_ID = o.ORDER_ID
INNER JOIN MASTERS m
ON m.PART_ID = o.PART_ID
END
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
February 16, 2012 at 10:37 pm
Doesn't seem to work, I'll try to modify it and see what happens.
Thanks
February 16, 2012 at 11:53 pm
Could be that o.PART_ID as the join criteria on MASTERS should be i.PART_ID.
Of course, it won't update anything if the PART_ID is not in MASTERS.
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
February 17, 2012 at 12:49 am
saw that one. That's great.
one more thing, I need to make a inner join on MASTERS.PART_ID = ORDERS.PART_ID AND MASTERS.CODE = ORDERS.CODE
February 17, 2012 at 1:16 am
Data on CODE and SIZE are just repeating.
February 17, 2012 at 1:36 am
Your last two posts don't make sense to me. Please will you post some sample INSERT statements and show what the new rows in ORDERS should look like after the trigger has fired?
I'm a bit concerned here that you're breaking the rules of normalisation by storing the same information about a part in two different tables. Is there a reason for doing that?
John
February 17, 2012 at 1:58 am
Hi John, sorry if I confused you.
My 1st post indicate the tables(MASTERS and ORDERS) and data of the database.
The MASTERS table already have some data just like this:
INSERT INTO MASTERS
VALUES ('1000','A100','10','TYPE A')
INSERT INTO MASTERS
VALUES ('1001','A101','12','TYPE A')
INSERT INTO MASTERS
VALUES ('1002','A200','20','TYPE B')
INSERT INTO MASTERS
VALUES ('1003','A210','22','TYPE B')
INSERT INTO MASTERS
VALUES ('1004','A235','30','TYPE C')
INSERT INTO MASTERS
VALUES ('1005','A525','50','TYPE D')
INSERT INTO MASTERS
VALUES ('1010','B100','90','TYPE G')
INSERT INTO MASTERS
VALUES ('1011','A550','100','TYPE G')
Then we will go to the ORDERS table and insert some data on 3 columns.
ORDER_ID, PART_ID and QUANTITY.
INSERT INTO ORDERS
VALUES ('A1','1000','100',NULL,NULL)
From that, I want to auto copy the CODE and SIZE from the MASTERS table.
The code from dwain seems to be incomplete because the data on CODE and SIZE are repeating regardless of PART_ID. Thanks for the inputs
February 17, 2012 at 2:10 am
This one works:
ALTER TRIGGER [dbo].[ORDERS_tg]
ON [dbo].[ORDERS]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE (PART_ID)
UPDATE o
SET CODE = m.CODE
,SIZE = m.SIZE
FROM ORDERS o
INNER JOIN Inserted i ON i.PART_ID = o.PART_ID
INNER JOIN MASTERS m ON m.PART_ID = i.PART_ID
END
But, how will I make this work on inner join MASTERS.PART_ID = ORDERS.PART_ID AND ORDERS.CODE = MASTERS.CODE
February 17, 2012 at 2:12 am
If I run this:
CREATE TABLE MASTERS
(PART_ID CHAR(6),
CODE CHAR(6),
SIZE NUMERIC(10,2),
ITEM_DESCRIPTION CHAR(50))
CREATE TABLE ORDERS
(ORDER_ID CHAR(6),
PART_ID CHAR(6),
QUANTITY NUMERIC(10,0),
CODE CHAR(6),
SIZE NUMERIC(10,2))
INSERT INTO MASTERS
VALUES ('1000','A100','10','TYPE A')
INSERT INTO MASTERS
VALUES ('1001','A101','12','TYPE A')
INSERT INTO MASTERS
VALUES ('1002','A200','20','TYPE B')
INSERT INTO MASTERS
VALUES ('1003','A210','22','TYPE B')
INSERT INTO MASTERS
VALUES ('1004','A235','30','TYPE C')
INSERT INTO MASTERS
VALUES ('1005','A525','50','TYPE D')
INSERT INTO MASTERS
VALUES ('1010','B100','90','TYPE G')
INSERT INTO MASTERS
VALUES ('1011','A550','100','TYPE G')
GO
CREATE TRIGGER dbo.ORDERS_tg
ON dbo.ORDERS
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE o
SET CODE = m.CODE
,SIZE = m.SIZE
FROM ORDERS o
INNER JOIN Inserted i
ON i.ORDER_ID = o.ORDER_ID
INNER JOIN MASTERS m
ON m.PART_ID = i.PART_ID
END
GO
INSERT INTO ORDERS
VALUES ('A1','1000','100',NULL,NULL)
SELECT * FROM ORDERS
I get this output set:
ORDER_ID PART_ID QUANTITY CODE SIZE
A1 1000 100 A100 10.00
Isn't that right for CODE and SIZE?
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
February 17, 2012 at 2:16 am
If you want to join as you say, there's no need setting Code:
ALTER TRIGGER dbo.ORDERS_tg
ON dbo.ORDERS
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE o
SET SIZE = m.SIZE
FROM ORDERS o
INNER JOIN Inserted i
ON i.ORDER_ID = o.ORDER_ID
INNER JOIN MASTERS m
ON m.PART_ID = i.PART_ID and m.Code = i.Code
END
GO
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
February 17, 2012 at 2:26 am
I understand what you're trying to do, although I don't know what you meant with that partial code about the join. However, please, please don't do it like this. The code and size are already stored in your MASTERS table, so you don't need therm repeated in ORDERS. You're wasting space in your database and setting yourself up for a whole load of integrity issues. If you need to know the code and size of the part in a particular order, you can join back to the MASTERS table.
John
February 17, 2012 at 2:27 am
Oops - wrong thread! Don't know how that happened.
John
February 17, 2012 at 2:31 am
John - You're way over my head with that.
Maybe grasshopper wants both fields in the join because a PART_ID can have more than one CODE?
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
February 17, 2012 at 2:57 am
dwain.c (2/17/2012)
Maybe grasshopper wants both fields in the join because a PART_ID can have more than one CODE?
Maybe, but that doesn't show up in the sample data he posted.
You're way over my head with that
The original poster is treating SQL Server as a spreadsheet rather than a relational DBMS. Here is how it should be done, with my changes to the DDL shown in bold. I'm assuming that PART_ID is unique.
CREATE TABLE MASTERS
(PART_ID CHAR(6) PRIMARY KEY,
CODE CHAR(6),
SIZE NUMERIC(10,2),
ITEM_DESCRIPTION CHAR(50))
CREATE TABLE ORDERS
(ORDER_ID CHAR(6),
PART_ID CHAR(6),
QUANTITY NUMERIC(10,0)
-- no need for CODE and SIZE in this table
FOREIGN KEY (PART_ID) REFERENCES MASTERS(PART_ID)
)
INSERT INTO MASTERS
VALUES ('1000','A100','10','TYPE A')
INSERT INTO MASTERS
VALUES ('1001','A101','12','TYPE A')
INSERT INTO MASTERS
VALUES ('1002','A200','20','TYPE B')
INSERT INTO MASTERS
VALUES ('1003','A210','22','TYPE B')
INSERT INTO MASTERS
VALUES ('1004','A235','30','TYPE C')
INSERT INTO MASTERS
VALUES ('1005','A525','50','TYPE D')
INSERT INTO MASTERS
VALUES ('1010','B100','90','TYPE G')
INSERT INTO MASTERS
VALUES ('1011','A550','100','TYPE G')
INSERT INTO ORDERS
VALUES ('A1','1000','100') -- CODE and SIZE not needed here
Notice no triggers are needed. Need a list of orders complete with part details? No problem:
SELECT
o.ORDER_ID
, m.PART_ID
, m.CODE
, m.SIZE
, m.ITEM_DESCRIPTION
FROM
ORDERS o
JOIN
MASTERS m ON o.PART_ID = m.PART_ID
John
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply