August 19, 2008 at 3:59 am
gohsiauken (8/18/2008)
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,
Tried to run your program in SQL server 2005, there is a error. So i modify the program as the following by adding a CURSOR to check every inserted row. Do you think it is the right way?
CREATE TRIGGER Product_tUpdateAfter
on dbo.Product AFTER update
As
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @Subject as varchar(200), @Description varchar(8000), @To varchar(50),
@From varchar(50), @ProductID int
SET @Subject= 'ALERT: Low Product Quantity!'
SET @Description = 'Please note that the following productare running low. Less than 5 quantity are available : '
SET @From= 'alert@mail.com'
SET @To= 'audrey@mail.com'
-- 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 int
set @QuantityLevel = 5
DECLARE curProduct CURSOR FOR
Select inserted.ProductID
--'Please note that Product ' + STR(inserted.ProductID) + ' is running low in quantity. Less than ' + STR(@QuantityLevel) + ' quantity is available.'
FROM inserted
where deleted.ProductID = inserted.ProductID
and deleted.TotalQuantity <> inserted.TotalQuantity
and inserted.TotalQuantity < @QuantityLevel
OPEN curMember
FETCH NEXT FROM curMember
INTO @ProductID
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Description IS NULL
SET @Description = 'Please note that the following productare running low. Less than 5 quantity are available : ' + CAST(@ProductID as varchar) + ', '
ELSE
SET @Description = @Description + + CAST(@ProductID as varchar) + ', '
FETCH NEXT FROM curMember
INTO @ProductID
END
CLOSE curMember
DEALLOCATE curMember
--Insert into Email Table
Insert Into EmailData([From],[To],[Subject],[Description],CreatedOn)
values (@From,@To,@Subject,@Description,getdate())
END
GO
I have anohter question about trigger, does SQL server has AFTER [Insert, Delete]? Examples as
CREATE TRIGGER Product_tUpdateAfter800
on dbo.Product AFTER update
CREATE TRIGGER Product_tInsertAfter800
on dbo.Product AFTER Insert
CREATE TRIGGER Product_tIDeleteAfter800
on dbo.Product AFTER Delete
If the answer is yes, then what is the value of "inserted' and "deleted"?
Thank you very much.
Regards,
Ken
August 19, 2008 at 4:52 am
gohsiauken (8/19/2008)
I have anohter question about trigger, does SQL server has AFTER [Insert, Delete]? Examples asCREATE TRIGGER Product_tUpdateAfter800
on dbo.Product AFTER update
CREATE TRIGGER Product_tInsertAfter800
on dbo.Product AFTER Insert
CREATE TRIGGER Product_tIDeleteAfter800
on dbo.Product AFTER Delete
If the answer is yes, then what is the value of "inserted' and "deleted"?
Yes, and the syntax is as you have. The virtual tables are still inserted and deleted. in an insert, inserted has the newly inserted rows and deleted is empty. In a delete, the inserted table is empty and deleted contains the rows that were deleted.
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 19, 2008 at 6:58 pm
Hi,
Then how about to have BEFORE Trigger? Like the following,
CREATE TRIGGER Product_tBeforeAfter800
on dbo.Product BEFORE update
CREATE TRIGGER Product_tBeforeAfter800
on dbo.Product BEFORE delete
CREATE TRIGGER Product_tBeforeAfter800
on dbo.Product BEFORE insert
Thanks
Ken
August 20, 2008 at 12:15 am
There aren't before triggers in SQL Server. Look up the Instead Of trigger in Books online for the other kind of trigger.
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
Viewing 4 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply