September 2, 2009 at 8:17 am
I want to create trigger which fires only when not null value is inserted into perticular column...
My requirement is:
table employee contanins three columns id,phNo,name... if new row inserted into employee table and if name is not null then fire trigger and copy that newly inserted row into another table...Do not fire trigger if name is null ..
main concern is want to fire trigger only after not null value insert into perticular column... if null value inserted into column then not to fire trigger.
September 2, 2009 at 8:51 am
Hi
You can use the UPDATE() function which is available within triggers:
USE tempdb
GO
IF (OBJECT_ID('TestTrigger') IS NOT NULL)
DROP TABLE TestTrigger;
GO
CREATE TABLE TestTrigger
(
Id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED
,SomeInt INT
,SomeVarChar VARCHAR(30)
);
GO
CREATE TRIGGER TR_TestTrigger ON TestTrigger
AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(SomeInt)
BEGIN
PRINT ' -> Trigger do Something';
END;
GO
GO
SET NOCOUNT ON;
PRINT 'Insert'
INSERT INTO TestTrigger
SELECT 1, 'blah';
PRINT 'Update SomeVarChar'
UPDATE TestTrigger SET SomeVarChar = 'bluff';
PRINT 'Update SomeInt'
UPDATE TestTrigger SET SomeInt = 3;
Greets
Flo
September 2, 2009 at 9:03 am
create trigger dbo.NameStuff on dbo.MySourceTable
after insert
as
set nocount on;
insert into dbo.MyTargetTable (Name)
select Name
from inserted
where Name is not null;
It'll look something like that. If you select from the "inserted" table where the name column is not null, it'll do what you need.
Flo, not sure if you know, but inserts count as updating every row, so using If Update() in an After Insert trigger is kind of meaningless.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 2, 2009 at 9:12 am
GSquared (9/2/2009)
create trigger dbo.NameStuff on dbo.MySourceTableafter insert
as
set nocount on;
insert into dbo.MyTargetTable (Name)
select Name
from inserted
where Name is not null;
It'll look something like that. If you select from the "inserted" table where the name column is not null, it'll do what you need.
Flo, not sure if you know, but inserts count as updating every row, so using If Update() in an After Insert trigger is kind of meaningless.
I think he does not want the trigger to be fired in the first place when the value is NULL. If I am correct about his requirement like this, then I dont think its there as this is almost value level trigger and not even column level trigger. Pardon me if I have misunderstood the requirement. Thanks.
---------------------------------------------------------------------------------
September 2, 2009 at 9:29 am
GSquared (9/2/2009)
Flo, not sure if you know, but inserts count as updating every row, so using If Update() in an After Insert trigger is kind of meaningless.
Thanks for the correction, Gus. Didn't think about the "insert" requirement.
September 3, 2009 at 7:01 am
Pakki (9/2/2009)
GSquared (9/2/2009)
create trigger dbo.NameStuff on dbo.MySourceTableafter insert
as
set nocount on;
insert into dbo.MyTargetTable (Name)
select Name
from inserted
where Name is not null;
It'll look something like that. If you select from the "inserted" table where the name column is not null, it'll do what you need.
Flo, not sure if you know, but inserts count as updating every row, so using If Update() in an After Insert trigger is kind of meaningless.
I think he does not want the trigger to be fired in the first place when the value is NULL. If I am correct about his requirement like this, then I dont think its there as this is almost value level trigger and not even column level trigger. Pardon me if I have misunderstood the requirement. Thanks.
There is no way to make a trigger fire based on a value. If the trigger exists, it will fire on the event(s) it is assigned to, regardless of what values are being supplied.
What this does is make it so the trigger doesn't DO anything for rows where the value is null, and does what's needed for rows where it is null.
A possibly better solution would be to write the code into the insert proc in the first place, but that's not necessarily reliable in all cases, if some applications have table-level access.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 3, 2009 at 11:01 am
Thanks GSquared 🙂
yeah, snehal.gamaji.career should not be bothered about the value for which the trigger fires as long as the purpose is achieved unless there is any special reason why he is looking for that kind of requirement.
---------------------------------------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply