Trigger Example

The below trigger discuss about INSERT and UPDATE Events on Table table will effect on same table. Below example discussed about the same scenario.

CREATE TABLE Table1
(ID INT IDENTITY(1,1),[Name] VARCHAR(50),AGE INT,Marks INT)
GO
CREATE TABLE Table2
(ID INT IDENTITY(1,1),[Name] VARCHAR(50),AGE INT,Marks INT)
GO
CREATE TRIGGER UpTodateTable2
ON Table1
FOR INSERT,UPDATE
AS
BEGIN
IF EXISTS(SELECT ID FROM Table1
WHERE ID NOT IN (SELECT ID FROM Table2 ))
BEGIN
INSERT INTO Table2
SELECT [Name],Age,Marks FROM Inserted
END
IF EXISTS(SELECT T2.ID FROM Table2 AS T2 INNER JOIN Table1 as T1 ON T1.ID=T2.ID)
BEGIN
UPDATE TABLE2
SET [NAME]=I.[NAME],
AGE=I.AGE,
MARKS=I.MARKS
FROM TABLE1 AS I INNER JOIN TABLE2 AS T2
ON T2.ID=I.ID
END
END
GO
INSERT INTO Table1
SELECT 'Mohan',30,20
GO
update Table1
SET [Name]='ReddyChinna'
WHERE id=2
GO
SELECT * FROM Table1
SELECT * FROM Table2

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server