August 31, 2009 at 2:49 pm
CREATE TRIGGER CustomerInsert
ON dbo.CustomerInfo
FOR insert
AS
declare @body varchar(2000)
declare @CustomerNewName varchar(10)
SELECT @CustomerNewName = First_Name
FROM inserted where last_name='ZZZZZZZZZZ'
SET @body = 'customerid inserted is'+ @CustomerNewName
EXEC master..xp_sendmail
@recipients = 'xxxxx@yyyyy.com',
@subject = 'Customer Information Updated',
@message = @body
This trigger is invoked each time any new row is inserted. I need a trigger which is invoked only if the where clause condition is satisfied. And also I have table populated with 10000 rows . There are rows where the condition in the trigger is satisfied. But I need the trigger to be invoked only for the new rows which will be inserted on not on the old rows already present in the table .
Thanks
August 31, 2009 at 3:00 pm
The trigger will always fire, and will only fire, when an INSERT takes place. You will have to put logic within the trigger to test for the other conditions, and determine what action(s) to take.
If you wanted to retroactively process existing rows, you would need to create a separate query or stored procedure to process them.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 1, 2009 at 11:23 am
Thats where I guess I need some assistance . Appreciate your response .
Thanks
September 1, 2009 at 12:14 pm
Here is an example that just logs the first names instead of emailing them, but you should get the idea.
You didn't mention whether or not you might ever do an INSERT that inserts multiple rows. If you do, your processing logic would have to change to store all the inserted rows and (sigh) execute a loop to email them one at a time. I personally would rather see one email per INSERT, and have the body contain the list of 'ZZZZZZZZZ' names that were inserted. The example below illustrates how names can slip past the trigger, if multiple rows are added with a single INSERT.
use Sandbox;
if object_id(N'dbo.TriggerTest') is not null DROP TABLE dbo.TriggerTest;
create table dbo.TriggerTest (ID int identity(1,1), First_Name varchar(20), Last_Name varchar(20));
if object_id(N'dbo.TriggerLog') is not null DROP TABLE dbo.TriggerLog;
create table dbo.TriggerLog (ID int identity(1,1), body varchar(100));
insert into dbo.Triggertest
select 'Aaron','Adams' union all
select 'Buddy','Baker' union all
select 'Charles','Carver'
-- select * from dbo.TriggerTest=
-- everything above is just to set the stage
GO
CREATE Trigger dbo.CustomerInsert on dbo.TriggerTest for INSERT
as
declare @body varchar(2000)
declare @CustomerNewName varchar(10)
declare @rc int
SELECT @CustomerNewName = First_Name
FROM inserted where last_name='ZZZZZZZZZZ'
SET @rc = @@ROWCOUNT
-------------------------------------------------------------------------------------------------
-- if a row is returned from the statement above, then execute everything between BEGIN and END
-------------------------------------------------------------------------------------------------
IF @rc > 0
BEGIN
SET @body = 'first name is '+ @CustomerNewName
insert into dbo.TriggerLog
select @body
END
GO
------- test the trigger
insert into dbo.TriggerTest
select 'Zipper','ZZZZZZZZZZ'
insert into dbo.TriggerTest
select 'Edgar','Evans'
-- if multiple-row inserts are possible, ZAPPER and ZOEY are going to get lost under the current logic
insert into dbo.TriggerTest
select 'Zinger','ZZZZZZZZZZ' union all
select 'Fred','Farnsworth' union all
select 'Zapper','ZZZZZZZZZZ' union all
select 'Zoey','ZZZZZZZZZZ'
select * from dbo.TriggerTest
select * from dbo.TriggerLog
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 1, 2009 at 4:21 pm
Thanks Mate..that helped !!
September 1, 2009 at 4:25 pm
You're welcome, and thanks for the feedback. Good luck to you.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply