Trigger for update

priya Avanigadda

11y1.1k

How to write the trigger for update??can any one send the procedure??

13 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pankaj Kumar Choudhary11y ago
Accepted answer
hello @priya now try this code

ALTER TRIGGER [dbo].[Trig_UserScore] ON [dbo].[UserGroupMapping]
FOR Update
AS
begin
Declare @Userid nvarchar(Max);
Declare @Groupid nvarchar(Max);

Select @UserId= tab.Userid From Inserted Tab;
Select @Groupid = tab.Groupid From Inserted Tab;

Update Userscores Set GroupId=@GroupId Where UserId=@UserId;

end
0
Pankaj Kumar Choudhary11y ago
hello @Priya here tab is an alias of Inserted Or Deleted table

inserted and deleted both are known as magic table
Inserted magic table contain data that is insert into table
and Deleted magic table contain data that is deleted from table

here

Select @UserId= Inserted.Userid from Inserted
Select @UserId = tab.@UserId From Inserted Tab;

both work same there is no diff b/w both statements....

if you want to learn more about Magic table and trigger then you can read my following article

Magic (Virtual) Tables in SQL
0
priya Avanigadda11y ago
Here what is the Tab??
0
Pankaj Kumar Choudhary11y ago
please check your trigger and reply me if following trigger and command is not according as per your requirement .... i well modify
0
priya Avanigadda11y ago
Thnk u soo much... It's working
0
Pankaj Kumar Choudhary11y ago
Hello @Priya this method is working correctly very well for me

this is Trigger
USE [Home_Management]
GO

/****** Object: Trigger [dbo].[Trig_UserScore] Script Date: 6/2/2015 4:49:30 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create TRIGGER [dbo].[Trig_UserScore] ON [dbo].[UserGroupMapping]
FOR Update
AS
begin
Declare @Userid nvarchar(Max);
Declare @Groupid nvarchar(Max);

Select @UserId= tab.Userid From Inserted Tab;
Select @Groupid = tab.Groupid From Inserted Tab;

Update Userscores Set GroupId=@GroupId Where UserId=@UserId;

end
GO

These my table

After execution of Update command

0
priya Avanigadda11y ago
Noo Its not working...I can able to update the UserGroupMapping columns but at the same time it's not updating in UserScores table ..That means Trigger is not working
0
priya Avanigadda11y ago
Here is my Trigger for UserGroupMapping When I insert the values into UserId and GroupId it's automatically inserting into UserScores also(Which has same columns in UserScores)..So I need same action for update also

ALTER TRIGGER [dbo].[Trig_UserScore] ON [dbo].[UserGroupMapping]
FOR INSERT
AS
begin
INSERT INTO dbo.UserScores
(UserId,GroupId)
SELECT
UserId,GroupId
FROM inserted
end

0
Pankaj Kumar Choudhary11y ago
Dear @Priya i think you want to use same trigger for more then one table but one trigger can be implemented on a single table means you can use this same trigger for more then table like
Create Trigger After_Update On Student , Stdeunt1 ,Student3

but in a sinlr Trigger you can use more then tables
like

alter trigger [dbo].[trigger_Update] on [dbo].[UserGroupMapping]
after insert, update
as
begin
declare @UserId int,@GroupId int
Select @UserId=tab.UserId from Inserted tab
Select @GroupId=tab.GroupId from Inserted tab

update UserGroupMapping set UserId=@UserId where GroupId=@GroupId

update UserGroupMapping1 set UserId=@UserId where GroupId=@GroupId
end

i am wrong then please write what is your actual query.........

0
priya Avanigadda11y ago
Thanks for ur reply..but the value is updating in one table only..it's not triggering in other table with the same column
0
Pankaj Kumar Choudhary11y ago
Hello @Priya try this

alter trigger [dbo].[trigger_Update] on [dbo].[UserGroupMapping]
after insert, update
as
begin
declare @UserId int,@GroupId int
Select @UserId=tab.UserId from Inserted tab
Select @GroupId=tab.GroupId from Inserted tab

update UserGroupMapping set UserId=@UserId where GroupId=@GroupId
end

0
priya Avanigadda11y ago
This is my procedure..but it's not updating the values...is der any changes

alter trigger [dbo].[trigger_Update] on [dbo].[UserGroupMapping]
after insert, update
as
declare @UserId int,@GroupId int
begin
update UserGroupMapping set UserId=@UserId where GroupId=@GroupId /*where UserId=@UserId*/
/*SELECT
UserId,GroupId
FROM inserted*/
end

0
Pankaj Kumar Choudhary11y ago
Hello @Priya
Types of DML Triggers

Triggers are mainly three types

1.    After Triggers

2.      Instead Of Triggers

After Triggers for Update

Create Trigger After_Update On Student

AFTER Update

AS

Declare @IId int ;

Declare @Name Nvarchar(50) ;

Declare @Age Int;

Declare @Class int;

Declare @IId1 int ;

Declare @Name1 Nvarchar(50) ;

Declare @Age1 Int;

Declare @Class1 int;

Select @IId= Tab.IId From Deleted Tab;

Select @Name= Tab.Name From Deleted Tab;

Select @Age= Tab.Age From Deleted Tab;

Select @Class= Tab.Class From Deleted Tab;

Select @IId1= Tab.IId From inserted Tab;

Select @Name1= Tab.Name From inserted Tab;

Select @Age1= Tab.Age From inserted Tab;

Select @Class1= Tab.Class From inserted Tab;

Set @IId1=@IId1+1;

if @IId>10

begin

Delete From Student Where IId=@iid

Insert Into Student(IId,Name , Age ,Class) Values (@IId1,@Name1,@Age1,@Class1)

Print 'Data Updated Successfully'

End

else

Begin

Print 'Data not Updated Successfully'

End

Update trigger for Instead Of

Create Trigger Insted_Update On Student

Instead Of Update

as

begin

Declare @IId int ;

Declare @Name Nvarchar(50) ;

Declare @Age Int;

Declare @Class int;

Select @IId= Tab.IId From Deleted Tab;

Select @Name= Tab.Name From Deleted Tab;

Select @Age= Tab.Age From Deleted Tab;

Select @Class= Tab.Class From Deleted Tab;

Delete From Student Where IId=@IId

Set @IId=@IId+1;

Insert Into Student(IId,Name , Age ,Class) Values (@IId,@Name,@Age,@Class)

Print 'Data Updated Successfully'

End
Make changed in these SP according your requirement
0