?
Solved

trigger

Posted on 2011-09-07
4
Medium Priority
?
297 Views
Last Modified: 2012-05-12
I have two tables, table A and table B. When ever row is updated , inserted or deleted in table A, same row should be updated , inserted and deleted in table B. Want to write insert , update and delete after trigger on table A and in trigger update data in table b.

thanks,
Ravi
0
Comment
Question by:asrithap
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36500906
0
 

Author Comment

by:asrithap
ID: 36501020
Can you give me examples for update, insert and delete trigger.
0
 
LVL 9

Expert Comment

by:mimran18
ID: 36501021
0
 
LVL 9

Accepted Solution

by:
mimran18 earned 2000 total points
ID: 36501039
Here is the sample.

 
Drop table test1
Go
Create table test1
(ID int,
[SName] nvarchar(50),
)
Go

Drop table test2
Go
Create table test2
(ID int,
[SName] nvarchar(50),
)
Go

Open in new window

Drop TRIGGER test_INSERT 
GO
CREATE TRIGGER test_INSERT 
ON test1
FOR INSERT, UPDATE, DELETE AS 
 
SET NOCOUNT ON; 

-- 
-- Variables Needed for this Trigger 
-- 
DECLARE @ID int

DECLARE @SName varchar(50) 

-- -- Determine if this is an INSERT,UPDATE, or DELETE Action --

DECLARE @Action as char(1) 
DECLARE @Count as int 
SET @Action = 'I' 
-- Set Action to 'I'nsert by default. 
SELECT @Count = COUNT(*) FROM DELETED 
if @Count > 0     
	BEGIN         
		SET @Action = 'D' -- Set Action to 'D'eleted.         
		SELECT @Count = COUNT(*) FROM INSERTED         
		IF @Count > 0 SET @Action = 'U' -- Set Action to 'U'pdated.     
	END

if @Action = 'D'     
-- This is a DELETE Record Action     
--     
BEGIN         
	SELECT @ID =[ID]                     
    FROM DELETED  
        
	DELETE [dbo].test2         
	WHERE [ID]    =@ID 

	END  
Else     
BEGIN             
		--             
		-- Table INSERTED is common to both the INSERT, UPDATE trigger             
		--             
		SELECT @ID =[ID]                 
		,@Sname = [Sname] FROM INSERTED      
      
		if @Action = 'I'-- This is an Insert Record Action             
		--             
		BEGIN                 
				insert test2 SELECT  [ID],[SName] FROM inserted           
		END         
		else             
		-- This is an Update Record Action             
		--             
		BEGIN                 
				UPDATE test2            
				SET [Sname] = @Sname 
				WHERE [ID]=@ID 
    	END 
END

Open in new window


 
Insert  into test1 values (1,'John')
Insert  into test1 values (2,'Robert')
Insert  into test1 values (3,'William')
GO
Select * from test1
Select * from test2
GO
Update [test1] set [Sname]='Robinson' where [ID]=2
GO
Select * from test1
Select * from test2
GO
Delete from test1 Where [ID]=1
GO
Select * from test1
Select * from test2

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question