-
Feb 23rd, 2011, 02:46 AM
#1
Thread Starter
Lively Member
[RESOLVED] Trigger Problem
Hi everybody
In my case I just want trigger to work after row inserted when status = 16 or row updated to status 16 but the problem is when any modification is made in any column the trigger will be running but that is wrong in my case.
here is my trigger:
Code:
ALTER TRIGGER [dbo].[TRIG_CopyRegularEmp]
ON [dbo].[Table1]
AFTER INSERT,UPDATE
AS
BEGIN
Declare @ID int
select @ID = ID from inserted
INSERT INTO Table2
(Table2.ID, Table2.Name, Table2.Status, Table2.Job)
SELECT Table1.ID, Table1.Name, Table1.Status,Table1.Job
FROM Table1
WHERE (Table1.Status = 16)
AND (Table1.ID = @ID)
END
can anyone correct trigger to achieve my case?
bintaleb
-
Feb 24th, 2011, 01:58 AM
#2
New Member
Re: Trigger Problem
Please try the following
Code:
ALTER TRIGGER [dbo].[TRIG_CopyRegularEmp]
ON [dbo].[Table1]
AFTER INSERT,UPDATE
AS
BEGIN
Declare @ID int
Declare @status int
select @ID = ID, @status = status from inserted
If (@status =16)
BEGIN
INSERT INTO Table2
(Table2.ID, Table2.Name, Table2.Status, Table2.Job)
SELECT Table1.ID, Table1.Name, Table1.Status,Table1.Job
FROM inserted
WHERE (inserted.id = @ID)
END
END
-
Feb 24th, 2011, 04:41 PM
#3
Thread Starter
Lively Member
Re: Trigger Problem
Hello there
Thank you so much for help but also not OK.
bintaleb
-
Feb 25th, 2011, 03:37 AM
#4
New Member
Re: Trigger Problem
I think i missed out the comparision sorry, please try the following
Code:
ALTER TRIGGER [dbo].[TRIG_CopyRegularEmp]
ON [dbo].[Table1]
AFTER INSERT,UPDATE
AS
BEGIN
Declare @ID int
Declare @OldStatus int, @NewStatus Int
select @ID = ID, @NewStatus = status from inserted
select @OldStatus = Status from deleted
If (@NewStatus =16 AND @OldStatus!=@NewStatus)
BEGIN
INSERT INTO Table2
(Table2.ID, Table2.Name, Table2.Status, Table2.Job)
SELECT inserted.ID, inserted.Name, inserted.Status,inserted.Job
FROM inserted
WHERE (inserted.id = @ID)
END
END
--Nagendra
Last edited by Nagendra Rao; Feb 25th, 2011 at 04:00 AM.
-
Feb 25th, 2011, 07:12 PM
#5
Thread Starter
Lively Member
Re: Trigger Problem
Thank you so much that is what I exactly looking for.
bintaleb
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|