November 3, 2009 at 5:23 pm
Hi all (sorry if this applies to the wrong section),
I'm having some trouble with a basic insert trigger here. For simplicity, let's say I have 2 tables dealing with Insurance Sales.
One table with Customer info (named Customer) and the other dealing with the representatives (named Rep) who serve the customers.
All I'm looking to do is to have a column in the Rep table(that counts the number of customers a rep serves, call it CustomerCount) incremented after a new record is added to the customer table. So after I insert a new customer table record, Rep #1 say will increment from 4 to 5.
Basically, I'm just confused with the syntax. Here is what I have so far based from a book I have:
CREATE TRIGGER CustomerCountInc
AFTER INSERT ON Customer
FOR EACH ROW
BEGIN
UPDATE Rep
SET CustomerCount = CustomerCount + New.CustomerCount
WHERE RepNum = New.RepNum ;
END
Doubt that is correct, but I get this when I try to execute it:
Msg 102, Level 15, State 1, Procedure CustomerCountInc, Line 2
Incorrect syntax near 'AFTER'.
Can someone help me out here with a push in the right direction? I have way overthought this and now I'm just confused =(
Thanks for reading.
November 3, 2009 at 6:08 pm
Hi,
I may sound too harsh but please go ahead and reply with your table structure (Create Table script )of both the tables first and also insert script into the 2 table (2 or 3 rows is enough) and then explain what you need, I would be glad to help.
Of Course some one may reply to your question, but this would help you understand how to post forum questions related to scripts and SQL Code..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 3, 2009 at 6:31 pm
two things..first, your trigger model is for ORACLE/PLSQL..SQL Server is slightly different.
second it just a logical solution...instead of updating a static table because of an INSERT, you could just replace it with a view that automatically calculates the count based on the original table. the view would have the advantage of never being inaccurate.
a correct example of the trigger would be like this: the big difference is when the insert occurs, wether it is one row or a million rows, the update is a set based solution.
CREATE TRIGGER TR_CustomerCountInc ON Customer
FOR INSERT
AS
BEGIN
UPDATE Rep
SET CustomerCount = CustomerCount + X.THECOUNT
FROM (SELECT COUNT(RepNum ) AS THECOUNT,RepNum FROM INSERTED GROUP BY RepNum ) X
WHERE Rep.RepNum = X.RepNum ;
END
a simple example of the view would start off with something like this:
CREATE VIEW RepCustomerCounts
AS
SELECT Rep.RepNum,SUM(CustomerCount.RepNum ) AS CustomerCount
FROM REP
LEFT OUTER JOIN Customer ON Rep.RepNum = Customer.RepNum
GROUP BY Rep.RepNum
END
Lowell
November 3, 2009 at 6:35 pm
Bru Medishetty (11/3/2009)
Hi,I may sound too harsh but please go ahead and reply with your table structure (Create Table script )of both the tables first and also insert script into the 2 table (2 or 3 rows is enough) and then explain what you need, I would be glad to help.
Of Course some one may reply to your question, but this would help you understand how to post forum questions related to scripts and SQL Code..
Nah, not harsh at all man....pretty sure I'm just way overthinking this. I apologize for the vagueness. It's just that it seems like theres several variations of the syntax to how to create a trigger and I just can't put it all together. Is this what your looking for ?
CREATE TABLE Rep
(RepNum CHAR(2) PRIMARY KEY,
LastName CHAR(15),
FirstName CHAR(15),
Street CHAR(15),
City CHAR(15),
State CHAR(2),
Zip CHAR(5),
Commission DECIMAL(7,2),
Rate DECIMAL(3,2) )
;
CREATE TABLE Customer
(CustomerNum CHAR(3) PRIMARY KEY,
CustomerName CHAR(35) NOT NULL,
Street CHAR(15),
City CHAR(15),
State CHAR(2),
Zip CHAR(5),
Balance DECIMAL(8,2),
CreditLimit DECIMAL(8,2),
RepNum CHAR(2) )
INSERT INTO Rep
VALUES
('20','Kaiser','Valerie','624 Randall','Grove','FL','33321',20542.50,0.05);
INSERT INTO Rep
VALUES
('35','Hull','Richard','532 Jackson','Sheldon','FL','33553',39216.00,0.07);
INSERT INTO Rep
VALUES
('65','Perez','Juan','1626 Taylor','Fillmore','FL','33336',23487.00,0.05);
INSERT INTO Customer
VALUES
('148','Al''s Appliance and Sport','2837 Greenway','Fillmore','FL','33336',6550.00,7500.00,'20');
INSERT INTO CUSTOMER
VALUES
('282','Brookings Direct','3827 Devon','Grove','FL','33321',431.50,10000.00,'35');
INSERT INTO CUSTOMER
VALUES
('356','Ferguson''s','382 Wildwood','Northfield','FL','33146',5785.00,7500.00,'65');
;
I've also altered the customer table by adding the previously mentioned CustomerCount column and then putting in the # of customers each rep has
ALTER TABLE Rep
ADD CustomerCount CHAR(2)
update Rep
set CustomerCount='xxx'
where RepNum = 'xxx'
So I'm just trying to create a trigger that when I add a new record into the Customer table, whatever rep number I use will increment the Rep table CustomerCount column. That make sense?
November 3, 2009 at 7:18 pm
Giving Full Credits to Lowell, the script he has mentioned is enough with a small change of REPNUM (3 Times in his Code)
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 3, 2009 at 7:25 pm
Thanks Bru; i posted my example before the schema got posted, and i edited my examples to use RepNum
Lowell
November 3, 2009 at 7:30 pm
Appreciate the responses fellas. I mean so what then would I do to alter it for a delete and update trigger ? Wasn't quite expecting what you gave.
With these types of trigs can I have more than one on a single given table? I think I remember reading that it would be okay.
November 3, 2009 at 7:43 pm
i would still strongly recommend using a view instead of triggers: it will never be wrong.
CREATE VIEW VW_REP
AS
SELECT
Rep.*,MyAlias.CustomerCount
From Rep
Left Outer Join (Select Count(CustomerNum)AS CustomerCount, RepNum From Customer Group By RepNum ) MyAlias
ON Rep.RepNum = MyAlias.RepNum
but for reference, here's what i would try as triggers:
for reference, two virtual tables exist for the duration of the INSERT/UPDATE/DELETE operation...these virtual tables are named [INSERTED] and [DELETED]; [INSERTED has the new values(for insert/update), while [DELETED] has the old values(for update/delete)
it's critical in a trigger to refer to these virtual tables, and assume more than one row will exist that need to be processed.
CREATE TRIGGER TR_CustomerCountInc ON Customer
FOR INSERT
AS
BEGIN
UPDATE Rep
SET CustomerCount = ISNULL(CustomerCount,0) + X.THECOUNT
FROM (SELECT COUNT(REPNBR) AS THECOUNT,REPNBR FROM INSERTED GROUP BY REPNBR) X
WHERE Rep.RepNum = X.RepNum ;
END
GO
CREATE TRIGGER TR_CustomerCountUpd ON Customer
FOR UPDATE
AS
BEGIN
UPDATE Rep
SET CustomerCount = ISNULL(CustomerCount,0)+ X.THECOUNT
FROM (SELECT COUNT(REPNBR) AS THECOUNT,REPNBR FROM INSERTED GROUP BY REPNBR) X
WHERE Rep.RepNum = X.RepNum ;
UPDATE Rep
SET CustomerCount = ISNULL(CustomerCount,0)- X.THECOUNT
FROM (SELECT COUNT(REPNBR) AS THECOUNT,REPNBR FROM DELETED GROUP BY REPNBR) X
WHERE Rep.RepNum = X.RepNum ;
END
GO
CREATE TRIGGER TR_CustomerCountdel ON Customer
FOR DELETE
AS
BEGIN
SET CustomerCount = ISNULL(CustomerCount,0)- X.THECOUNT
FROM (SELECT COUNT(REPNBR) AS THECOUNT,REPNBR FROM DELETED GROUP BY REPNBR) X
WHERE Rep.RepNum = X.RepNum ;
END
Lowell
November 3, 2009 at 7:46 pm
So you mean,when some one updates the Customer Table column RepNum from 65 to 20 again it should update the counts in the Rep table and also like wise, if a record for a RepNum 20 is deleted his CustomersCount has to be updated.
Did I get it right?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 3, 2009 at 7:50 pm
I came back and somemore script for Update and Delete Triggers is added by Lowell, that should suit you.
But as he said, a View is going to be the best solution since it needs multiple updates every time a record is inserted, updated or deleted.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 3, 2009 at 7:51 pm
Bru Medishetty (11/3/2009)
So you mean,when some one updates the Customer Table column RepNum from 65 to 20 again it should update the counts in the Rep table and also like wise, if a record for a RepNum 20 is deleted his CustomersCount has to be updated.Did I get it right?
i think so...if some customer's rep was changed from rep#65 to Rep#20, you'd expect one to go down, and the other to go up...
if Rep#65 got fired, and all 101 customers got moved to Rep#20 in a single UPDATE operation, you'd expect the final results to be Rep65=0, Rep20 = his current customers count plus 101 new ones.
yep that's what that suite of triggers would do, assuming the
Customer count was initialized to the current values at some popint prior to the trigger getting created.
Lowell
November 3, 2009 at 8:02 pm
Yep, you got it that's what I meant. Great insight guys, I'm gonna play around with those codes in SQLS and see if I can break them down and understand them further. Interesting angle on the views too. Again, thanks. I prob will still have a few more questions for ya 🙂
For future reference, does this forum have a resolved (or something similar) setting to hit once I understand this stuff a bit more?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply