June 29, 2011 at 4:15 am
Hi
i'm creating a trigger that runs every time a new stock code is created. I want the trigger to A) insert a record into another table. B) send an email to people letting them know they need to action the new entry
I only need to carry out the above if the stock code is a chemical though. how do i go about doing this?? The stock code will have the prefix 'Y%' in the inserted table. can i test for this first before running the rest of the trigger.
Thanks
June 29, 2011 at 4:22 am
You have the information with you! No worries then...
You can create the Trigger on table and check if stock code is prefix with prefix 'Y%' in magic tables. Thats it - if found insert insert a record into another table and send an email.
Magic tables are nothing but inserted and deleted tables in Triggers.
Abhijit - http://abhijitmore.wordpress.com
June 29, 2011 at 4:57 am
Hi
i'm not sure where to carry out the test. I'm from a vb background, will there be some kind of
select stockcode from inserted
if stockcode like 'Y%' then
do something here
end if
here's what i have so far.
CREATE TRIGGER dbo.trg_nexus_insert
ON dbo.InvMaster
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN
--Declare variables and set them to values from the inserted table
DECLARE @DISTchar(max);-- Distribution List
DECLARE @StockCodechar(30);-- StockCode
DECLARE @ActiveExpchar(1);-- 1/2% Tolerance
DECLARE @Hazardouschar(1);-- Hazardous
DECLARE @Powderchar(1);-- Powder
DECLARE @Booth2char(1));-- Booth 2?
DECLARE @NexusWeighchar(1);-- Allowed weigh method
DECLARE @SpareAchar(1);-- spare
DECLARE @SpareBchar(1);-- spare
DECLARE @AutoPostchar(1);-- autopost allowed?
DECLARE @MaintFlagsmallint();-- system generated flag
SELECT @StockCode= i.StockCode FROM inserted i;
SELECT @ActiveExp= 'A';
SELECT @Hazardous= 'Y';
SELECT @Powder= 'Y';
SELECT @Booth2= 'Y';
SELECT @NexusWeigh= 'Y';
SELECT @SpareA= '';
SELECT @SpareB= '';
SELECT @AutoPost= '4';
SELECT @MaintFlag= 0;
SET @DIST = 'myemail@wherever.co.uk'
BEGIN
-- INSERT STATEMENT WILL BE HERE
-- EMAIL STATEMENT WILL BE HERE
END
END
END
June 29, 2011 at 5:15 am
a trigger runs with every update of course, but the action that it does inside might be limited based on some logic.
also you've got to consider multiple rows in a trigger;
I'd consider something like this instead:all im using is one variable to get a count of # matching records.
CREATE TRIGGER dbo.trg_nexus_insert
ON dbo.InvMaster
AFTER INSERT
AS
BEGIN
DECLARE @SpecialStockCount int
SELECT @SpecialStockCount =Count(*) FROM INSERTED WHERE StockCode LIKE 'Y%'
IF @SpecialStockCount > 0
BEGIN
declare @body1 varchar(max)
SET @body1 = ' A Total of ' + convert(varchar,@SpecialStockCount) + ' records was inserted for a special stock code. please review the orders for details.'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='My Default DBMail Profile',
@recipients='lowell@somedomain.net',
@subject = 'SQl 2008 email test',
@body = @body1,
@body_format = 'HTML'
END --IF
END --TRIGGER
Lowell
June 29, 2011 at 9:05 am
great, this worked a treat thanks.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply