Trigger for update

Question

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

Pankaj Kumar Choudhary · 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

Pankaj Kumar Choudhary · Answer

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

priya Avanigadda · Answer

Here what is the Tab??

Pankaj Kumar Choudhary · Answer

please check your trigger and reply me if following trigger and command is not according as per your requirement .... i well modify

priya Avanigadda · Answer

Thnk u soo much... It's working

Pankaj Kumar Choudhary · Answer

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

priya Avanigadda · Answer

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

priya Avanigadda · Answer

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

Pankaj Kumar Choudhary · Answer

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.........

priya Avanigadda · Answer

Thanks for ur reply..but the value is updating in one table only..it's not triggering in other table with the same column

Pankaj Kumar Choudhary · Answer

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

priya Avanigadda · Answer

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

Pankaj Kumar Choudhary · Answer

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

Trigger for update

Answers (13)