August 15, 2008 at 8:15 am
Hi all,
I am a newbie in SQL scripts, appreciate if anyone can help.
I have a table "SALEITEM", which has 4 column "SaleID,ProductID,Quantity,UnitSellingPrice". I would like to create a Trigger in this table, after a new row is inserted into the "SALEITEM' table, it will deduct the total quantity in inventory for that item. Then it will check total quantity, if less than 5, it will write to EmailData table. The script as the following:
CREATE TRIGGER NewSaleItem
AFTER INSERT ON SALEITEM
REFERENCING NEW ROW AS newRow
FOR EACH ROW
AS
BEGIN
SET NOCOUNT ON;
Declare @UpdatedQty int,@Subject as varchar(200), @Body varchar(8000), @To varchar(50),
@From varchar(50),@ProductID int
SET @UpdatedQty=NULL
SET @Subject=NULL
SET @Body=NULL
SET @To=NULL
SET @From=NULL
SET @ProductID=newRow.ProductID
UPDATE PRODUCT
SET TotalQuantity = (TotalQuantity - newRow.Quantity)
WHERE ProductID = @ProductID;
--If updated qty less than 5 then trigger email
SELECT @UpdatedQty=[dbo].[fnCheckProductQuantity](@ProductId)
IF @UpdatedQty<5
BEGIN
SET @Subject= 'ALERT: Low Product Quantity'
SET @Body= 'Please note that Product ' + STR(@ProductID) + ' is running low in quantity. Less than 5 quantity is available.'
SET @From= 'abc@mail.com'
SET @To= 'bcd@mail.com'
--Insert into Email Table
insert into EmailData([From],[To],[Subject],[Description],CreatedOn)
values (@From,@To,@Subject,@Body,getdate())
END
END;
However, when i compile it in the SQL server management studio, i got this error "Msg 102, Level 15, State 1, Procedure NewSaleItem, Line 2
Incorrect syntax near 'AFTER'."
IS MS SQL support AFTER INSERT Trigger? Anyone has any ideal what went wrong?
Regards,
Ken
August 15, 2008 at 8:29 am
gohsiauken (8/15/2008)
CREATE TRIGGER NewSaleItem
AFTER INSERT ON SALEITEM
REFERENCING NEW ROW AS newRow
FOR EACH ROW
AS
That's not SQL syntax. Looks like it might be Oracle. Not sure.
Is this a SQL Server database, or is it another database type?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2008 at 8:50 am
Hi All,
I am sorry that mis-post the earlier question. Just get it updated.
If this is not the correct way to write a trigger, can anyone tell me hwow to create a trigger with my requirements??
Thank you very much,
Regards,
Ken
August 15, 2008 at 9:14 am
A few things that may help you get started. Firstly, if you check Books Online, you will see that the syntax for a trigger is as follows:
CREATE TRIGGER < Trigger Name > ON < Table Name >
AFTER < Operation to fire trigger >
AS
-- Code goes here
GO
In your code, you have the ON table name in the wrong place.
The section
REFERENCING NEW ROW AS newRow
FOR EACH ROW
is no T-SQL and has no equivalent. SQL Triggers fire once for an operation and the inserted/deleted tables will contain all the rows affected. Hence it is important to make sure that your code makes no assumptions about the number of rows in those tables.
The rest of your code looks OK, except it needs to be adapted to handle any number of rows in the inserted/deleted tables, not just one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2008 at 9:34 am
Hi, this is the edited version after your advice.
CREATE TRIGGER NewSaleItem
ON SALEITEM
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
Declare @UpdatedQty int,@Subject as varchar(200), @Body varchar(8000), @To varchar(50),
@From varchar(50),@ProductID int, @Quantity int
SET @UpdatedQty=NULL
SET @Subject=NULL
SET @Body=NULL
SET @To=NULL
SET @From=NULL
SET @ProductID=(select ProductID from inserted)
SET @Quantity=(select Quantity from inserted)
UPDATE PRODUCT
SET TotalQuantity = (TotalQuantity - @Quantity)
WHERE ProductID = @ProductID;
--If updated qty less than 5 then trigger email
SELECT @UpdatedQty=[dbo].[fnCheckProductQuantity](@ProductId)
IF @UpdatedQty<5
BEGIN
SET @Subject= 'ALERT: Low Product Quantity'
SET @Body= 'Please note that Product ' + STR(@ProductID) + ' is running low in quantity. Less than 5 quantity is available.'
SET @From= 'abc@mail.com'
SET @To= 'bcd@mail.com'
--Insert into Email Table
insert into EmailData([From],[To],[Subject],[Description],CreatedOn)
values (@From,@To,@Subject,@Body,getdate())
--Send Actual Email
exec dbo.Cust_send_cdosysmail @From, @To, @Subject, @Body
END
END;
Is it correct now???
Regards,
Ken
August 15, 2008 at 11:18 am
Better but you're still assuming that there will only be one row in the inserted tables, example the following line
SET @ProductID=(select ProductID from inserted)
There could be any number of rows in the inserted table. Consider how you will handle cases where two or more rows will be inserted into the SalesItem table in one insert statement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2008 at 1:53 pm
This is DB2 syntax. Not MS SQL Server and a Gail pointed above there is no "exact" equivalent in TSQL.
In addition sending email from a trigger is a very bad practice.
* Noel
August 16, 2008 at 1:36 am
GilaMonster (8/15/2008)
Better but you're still assuming that there will only be one row in the inserted tables, example the following line
SET @ProductID=(select ProductID from inserted)There could be any number of rows in the inserted table. Consider how you will handle cases where two or more rows will be inserted into the SalesItem table in one insert statement.
Hi
Thank for your reply.
To take care of multiple row insertion, can i use a CURSOR in the Trigger to filter which of the inserted row?
Regards,
Ken
August 16, 2008 at 4:19 am
You can.
What would probably be best is to make the entire thing handle an number of rows (set-based processing instead of row by row) and then use a cursor just for the dbo.Cust_send_cdosysmail proc, since that's the only thing that has to process one row at a time.
To get you started, consider this. I haven't included the send mail proc, but the rest should handle any number of rows in the inserted table
CREATE TRIGGER NewSaleItem
ON SALEITEM
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE PRODUCT
SET TotalQuantity = (TotalQuantity - i.Quantity)
FROM inserted i
WHERE i.ProductID = Product.ProductID
--Insert into Email Table if any updated quantity is less than 5
INSERT INTO EmailData([From],[To],[Subject],[Description],CreatedOn)
SELECT 'abc@mail.com','bcd@mail.com' ,'ALERT: Low Product Quantity' ,'Please note that Product ' + STR(ProductID) + ' is running low in quantity. Less than 5 quantity is available.', getdate())
FROM inserted where dbo.fnCheckProductQuantity(ProductID) < 5
-- TODO: Send the mails that have just been inserted into the EmailData table.
-- probably require a cursor, since the mail send is 1 row at a time
-- dbo.Cust_send_cdosysmail
END
GO
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 16, 2008 at 9:45 am
The trigger source you posted adheres to the SQL Standard but is not supported by SQL Server,
which uses a TransAct/SQL dialect. This is understandable, as SQL Server Triggers predate the
standard by about 15 years.
The SQL Standard Clause is "REFERENCING NEW ROW AS newRow"
SQL Server does not support this clause, but instead has a fixed option which is the equivalent to
"REFERENCING NEW ROW AS inserted"
The SQL Standard Clause is "FOR EACH ROW"
SQL Server does not support row level triggers, but instead has a fixed option which is the equivalent to:
"FOR EACH STATEMENT"
You will also find the SQL Standard Clause for when the trigger is to fire has options for BEFORE, AFTER,
or INSTEAD OF, but SQL Server does not support the BEFORE option and does not support allowing
the virtual table of "new row" to be updated.
Be warned that you need to be very careful that all SQL Statements handle multi-row actions correctly.
Here is example of the SQL necessary to maintain the PRODUCT.TotalQuantity when there are multiple rows
inserted into the SALESITEM table, which might be for the same ProductID:
UPDATE PRODUCT
SET TotalQuantity = (PRODUCT.TotalQuantity - NewRow.Quantity)
FROM (select inserted.ProductID
, sum(Quantity) as Quantity
from inserted
group by inserted.ProductID
HAVING sum(Quantity) <> 0
) as NewRow
WHERE Product.ProductID = NewRow.ProductID
SQL = Scarcely Qualifies as a Language
August 16, 2008 at 10:18 am
Additional comments:
The SQL Standard allows multiple triggers on a table for the same action
and allows specification of the order with the SEQUENCE keyword but
SQL Server allows multiple triggers but only allows specification of what trigger will fire first and last.
The logic to send an email when product.TotalQuantity is less than 5 should
not be in a trigger on the SaleItem table. There are many solutions offered under SQL Server:
1. Use Notification services.
2. Put all the logic in a trigger on the Product table
3. Put the detection logic in a trigger on the Product table, which inserts into a pending email table
and then have a SQL Server job that runs on a regular basis that sends the actual emails.
Using Notification services is the best practice as with a trigger, the only way to invoke the send mail routines is one at a time, which will require a cursor, and a cursor in a trigger will create a performance problem.
CREATE TRIGGER Product_tUpdateAfter800
-- Trigger names convention is the concatenation of TableName, underscore, "t", DML action, when and then a sequence number
on dbo.Product AFTER update
as
set nocount on
-- Note that the trigger is executed on the statement including the case when no rows are affected:
IF 0 = (select count(*) from inserted) RETURN
Declare @QuantityLevel integer
set @QuantityLevel = 5
--Insert into Email Table
insert into EmailData([From],[To],[Subject],[Description],CreatedOn)
select 'abc@mail.com' as From
, 'bcd@mail.com' as Tp
,'ALERT: Low Product Quantity' as Subject
, 'Please note that Product ' + STR(inserted.ProductID) + ' is running low in quantity. Less than ' + STR(@QuantityLevel) + ' quantity is available.'
as Description
, getdate() as createon
from inserted
join deleted
on deleted.ProductID = inserted.ProductID
where deleted.TotalQuantity <> inserted.TotalQuantity
and inserted.TotalQuantity < @QuantityLevel
go
SQL = Scarcely Qualifies as a Language
August 18, 2008 at 2:16 am
GilaMonster (8/16/2008)
You can.What would probably be best is to make the entire thing handle an number of rows (set-based processing instead of row by row) and then use a cursor just for the dbo.Cust_send_cdosysmail proc, since that's the only thing that has to process one row at a time.
To get you started, consider this. I haven't included the send mail proc, but the rest should handle any number of rows in the inserted table
CREATE TRIGGER NewSaleItem
ON SALEITEM
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE PRODUCT
SET TotalQuantity = (TotalQuantity - i.Quantity)
FROM inserted i
WHERE i.ProductID = Product.ProductID
--Insert into Email Table if any updated quantity is less than 5
INSERT INTO EmailData([From],[To],[Subject],[Description],CreatedOn)
SELECT 'abc@mail.com','bcd@mail.com' ,'ALERT: Low Product Quantity' ,'Please note that Product ' + STR(ProductID) + ' is running low in quantity. Less than 5 quantity is available.', getdate())
FROM inserted where dbo.fnCheckProductQuantity(ProductID) < 5
-- TODO: Send the mails that have just been inserted into the EmailData table.
-- probably require a cursor, since the mail send is 1 row at a time
-- dbo.Cust_send_cdosysmail
END
GO
Hi GilaMonster,
From your earlier post, if there are multiple rows are inserted at the same time, will the following code able to retreive each of the row inserted?
UPDATE PRODUCT
SET TotalQuantity = (TotalQuantity - i.Quantity)
FROM inserted i
WHERE i.ProductID = Product.ProductID
For example, i have 3 rows inserted, does it mean that i = 3 rows and the SQL is inteligent enough to retrieve row by row and update the Product table accordingly??
Thank for your help.
Regards,
Ken
August 18, 2008 at 2:29 am
Carl Federl (8/16/2008)
Additional comments:The SQL Standard allows multiple triggers on a table for the same action
and allows specification of the order with the SEQUENCE keyword but
SQL Server allows multiple triggers but only allows specification of what trigger will fire first and last.
The logic to send an email when product.TotalQuantity is less than 5 should
not be in a trigger on the SaleItem table. There are many solutions offered under SQL Server:
1. Use Notification services.
2. Put all the logic in a trigger on the Product table
3. Put the detection logic in a trigger on the Product table, which inserts into a pending email table
and then have a SQL Server job that runs on a regular basis that sends the actual emails.
Using Notification services is the best practice as with a trigger, the only way to invoke the send mail routines is one at a time, which will require a cursor, and a cursor in a trigger will create a performance problem.
CREATE TRIGGER Product_tUpdateAfter800
-- Trigger names convention is the concatenation of TableName, underscore, "t", DML action, when and then a sequence number
on dbo.Product AFTER update
as
set nocount on
-- Note that the trigger is executed on the statement including the case when no rows are affected:
IF 0 = (select count(*) from inserted) RETURN
Declare @QuantityLevel integer
set @QuantityLevel = 5
--Insert into Email Table
insert into EmailData([From],[To],[Subject],[Description],CreatedOn)
select 'abc@mail.com' as From
, 'bcd@mail.com' as Tp
,'ALERT: Low Product Quantity' as Subject
, 'Please note that Product ' + STR(inserted.ProductID) + ' is running low in quantity. Less than ' + STR(@QuantityLevel) + ' quantity is available.'
as Description
, getdate() as createon
from inserted
join deleted
on deleted.ProductID = inserted.ProductID
where deleted.TotalQuantity <> inserted.TotalQuantity
and inserted.TotalQuantity < @QuantityLevel
go
Hi Carl,
From your post, the method that you suggested is to let Product table handle the trigger. It means when a row is inserted in to SALEITEM table, the SALEITEM_tUpdate will be activated and update the Product table. And there is another trigger in PRODUCT table that will check whether the product quantity in less than 5. Correct me if i am wrong.
But i do not very understand how this part work. Since there is only update the product's quantity, why do we have a "deleted" in the code?
inserted
join deleted
on deleted.ProductID = inserted.ProductID
where deleted.TotalQuantity <> inserted.TotalQuantity
and inserted.TotalQuantity < @QuantityLevel
go
I have another question, when we use the trigger to update other table, how can we make sure that the data is in consistency? I afraid that after the data is inserted to SALEITEM table, then immediately there is a error occured before the trigger update the Product table.
Any suggestion would be much appreciate.
Regards,
Ken
August 18, 2008 at 2:40 am
Hi,
to your question "For example, i have 3 rows inserted, does it mean that i = 3 rows and the SQL is inteligent enough to retrieve row by row and update the Product table accordingly" - well, not precisely. SQL will not retrieve them row by row. SQL works with the entire set of 3 rows at once. This is probably the main difference from the way you are thinking about it. Don't think about separate rows when you work with SQL Server, think about rowsets.
UPDATE table SET col1='AAA' WHERE col2 = 'BBB' will update all corresponding rows in one chunk, not one after another. Therefore, if there is update trigger on Table, all updated rows will be "copied" to tables called "inserted" and "deleted" (entire rows, not just the updated columns). Inserted holds new values, deleted holds old values... and you can normally use JOIN to both these tables in triggers, so that the trigger action is also performed for all updated rows at once, not row by row.
August 18, 2008 at 3:54 am
From your post, the method that you suggested is to let Product table handle the trigger. It means when a row is inserted in to SALEITEM table, the SALEITEM_tUpdate will be activated and update the Product table. And there is another trigger in PRODUCT table that will check whether the product quantity in less than 5. Correct me if i am wrong.
You are correct - When the trigger is on the Product table, then any update to the TotalQuantity will invoke the notification logic.
But i do not very understand how this part work. Since there is only update the product's quantity, why do we have a "deleted" in the code?
The "deleted" table contains the rows as they appear before they are updated and the "inserted" the rows AFTER the update.
The join of the inserted to the deleted is used and then a where to determine if the value of TotalQuantity has changed.
As an update may NOT have changed the TotalQuantity, sending a subsequent email notifying that there is low inventory should not be performed.
SQL = Scarcely Qualifies as a Language
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply