Skip to content

SQL Trigger Inserted,Deleted Example

Information Table:

1JackEnderson5000Engineer
2MariaAllen7000Architect
3OliverPat4000Engineer
4RickQuenn5000Developer
5BobQuit7000Marketer
6AllenThin10000Architect
7SaraSait7000Engineer
8OsmanKaya5000Marketer
19megandavis6000Architect
9Ali            kaya           6000Developer
10jackquenn6000Engineer
11ameliataylor7000Marketer
12sophiewilson9000Architect
13avaJones9000Engineer
14thomaslee4000Architect
15lilydavies2000Developer
16jessicamartin5000Engineer
17miamorton4000Developer
18isebellaroberts8000Architect
20jenniferwhite7000Engineer
22IsabellaSmith5000Engineer
21jackallen6000Engineer
23AmeliaJones7000Architect
24OliviaWilliams9000Developer
25IslaBrown20000Developer
26PoppyTaylor12000Architect
27EmilyDavies7000Marketer
28JessicaWilson5000Engineer
29LilyEvans3000Marketer
30SophieThomas2000Architect
CREATE TABLE insert_person
(
	[ID] tinyint,
	[NAME] nvarchar(max),
	[SURNAME] nvarchar(max),
	[SALARY] int,
	[JOB] nvarchar(max)
)

CREATE TABLE delete_person
(
	[ID] tinyint,
	[NAME] nvarchar(max),
	[SURNAME] nvarchar(max),
	[SALARY] int,
	[JOB] nvarchar(max)
)

ALTER TRIGGER [insert] ON information 
AFTER INSERT
AS
BEGIN
	INSERT INTO insert_person
	SELECT Person_id,Person_FirstName,Person_LastName,Person_Salary,Person_Job FROM inserted
END

ALTER TRIGGER [deleted] ON information 
AFTER INSERT
AS
BEGIN
	INSERT INTO delete_person
	SELECT Person_id,Person_FirstName,Person_LastName,Person_Salary,Person_Job FROM deleted
END
INSERT INTO information
VALUES (45,'Jack','Allen',1000,'Marketer')

DELETE information
WHERE Person_id = 45

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: