Sql server 2000
Insert to insert
|
Insert to insert
CREATE TABLE MHS1 (
NPM char(10)
primary key,
nama
varchar(25),
no_hp
varchar(12)
)
go
CREATE TABLE MHS2 (
NPM char(10)
primary key,
nama
varchar(25),
no_hp
varchar(12)
)
go
create trigger tr_mhs1 on MHS1
for insert
as
begin
declare @NPM char(10)
declare @nama varchar(25)
declare @no_hp varchar(12)
select @NPM= NPM from inserted
select @nama= nama from inserted
select @no_hp= no_hp from inserted
insert into MHS2 values (@NPM, @nama, @no_hp)
end
insert into MHS1 values ('1134010061', 'pradhita yudha',
'085648182230')
select * from MHS2
|
Data pada table mhs1 di insert secara otomatis table mhs2
juga terisi
Insert to update
|
CREATE TABLE MHS3 (
NPM char(10)
primary key,
nama
varchar(25),
no_hp
varchar(12)
)
go
CREATE TABLE MHS4 (
NPM char(10)
primary key,
nama
varchar(25),
no_hp
varchar(12)
)
go
create trigger tr_mhs2 on MHS3
for insert
as
begin
declare @NPM char(10)
declare @nama varchar(25)
declare @no_hp varchar(12)
select @NPM= NPM from inserted
select @nama= nama from inserted
select @no_hp= no_hp from inserted
update MHS4 set nama=@nama, no_hp=@no_hp where NPM=@NPM
end
drop trigger tr_mhs2
insert into MHS3 values ('1134010061', 'indra bakti', '085648184450')
insert into MHS4 values ('1134010062', 'yudha', '085648182231')
insert into MHS4 values ('1134010063', 'negara', '085648182232')
select * from MHS4
|
Data di MHS4 sebelum insert di MHS3
Sesudah di insert
Insert to delete
|
CREATE TABLE MHS5 (
NPM char(10)
primary key,
nama
varchar(25),
no_hp varchar(12)
)
go
CREATE TABLE MHS6 (
NPM char(10)
primary key,
nama
varchar(25),
no_hp
varchar(12)
)
go
create trigger tr_mhs3 on MHS5
for insert
as
begin
declare @NPM char(10)
declare @nama varchar(25)
declare @no_hp varchar(12)
select @NPM= NPM from inserted
select @nama= nama from inserted
select @no_hp= no_hp from inserted
delete from MHS6 where NPM=@NPM
end
insert into MHS6 values ('1134010061', 'pradhita', '085648182230')
insert into MHS6 values ('1134010062', 'yudha', '085648182231')
insert into MHS6 values ('1134010063', 'negara', '085648182232')
insert into MHS5 values ('1134010061', 'pradhita', '085648182230')
select* from MHS6
|
Data di MHS4 sebelum insert di MHS5
Sesudah inser di mhs5 otomatis delete data yg di
mhs6
Update to insert
|
CREATE TABLE MHS7 (
NPM char(10)
primary key,
nama
varchar(25),
no_hp
varchar(12)
)
go
CREATE TABLE MHS8 (
NPM char(10)
primary key,
nama
varchar(25),
no_hp
varchar(12)
)
go
create trigger tr_mhs4 on MHS7
for update
as
begin
declare @NPM char(10)
declare @nama varchar(25)
declare @no_hp varchar(12)
select @NPM= NPM from inserted
select @nama= nama from inserted
select @no_hp= no_hp from inserted
insert into MHS8 values (@NPM, @nama, @no_hp)
end
insert into MHS6 values ('1134010061', 'pradhita', '085648182230')
insert into MHS6 values ('1134010062', 'yudha', '085648182231')
insert into MHS6 values ('1134010063', 'negara', '085648182232')
insert into MHS5 values ('1134010061', 'pradhita', '085648182230')
|
Sebelum mhs7 di update data di mhs8 masih kosong
Mhs7 setelah diupdate
Table mhs8 terisi data yg di update
Update to update
|
CREATE TABLE MHS9 (
NPM char(10)
primary key,
nama
varchar(25),
no_hp
varchar(12)
)
go
CREATE TABLE MHS10 (
NPM char(10)
primary key,
nama varchar(25),
no_hp
varchar(12)
)
go
create trigger tr_mhs5 on MHS9
for update
as
begin
declare @NPM char(10)
declare @nama varchar(25)
declare @no_hp varchar(12)
select @NPM= NPM from inserted
select @nama= nama from inserted
select @no_hp= no_hp from inserted
update MHS10 set nama=@nama, no_hp=@no_hp where NPM=@NPM
end
insert into MHS10 values ('1134010061', 'pradhita', '085648182230')
insert into MHS10 values ('1134010062', 'yudha', '085648182231')
insert into MHS10 values ('1134010063', 'negara', '085648182232')
update MHS9 set nama='haris cpt', no_hp='085683658123' where
npm='1134010061'
select* from MHS10
|
Data di table mhs9 dan mhs10 sama isinya
Setelah update di mhs9 maka mhs10 juga otomatis
terupdate
Update to delete
|
CREATE TABLE MHS11 (
NPM char(10)
primary key,
nama
varchar(25),
no_hp
varchar(12)
)
go
CREATE TABLE MHS12 (
NPM char(10)
primary key,
nama
varchar(25),
no_hp
varchar(12)
)
go
create trigger tr_mhs6 on MHS11
for update
as
begin
declare @NPM char(10)
declare @nama varchar(25)
declare @no_hp varchar(12)
select @NPM= NPM from inserted
select @nama= nama from inserted
select @no_hp= no_hp from inserted
delete from mhs12 where npm=@npm
end
insert into MHS12 values ('1134010061', 'pradhita', '085648182230')
insert into MHS12 values ('1134010062', 'yudha', '085648182231')
insert into MHS12 values ('1134010063', 'negara', '085648182232')
update MHS11 set nama='haris cpt', no_hp='085683658123' where
npm='1134010061'
select* from MHS12
|
Data di table mhs11 dan mhs12 sama isinya
Mhs11 setelah update
Mhs12 setelah update di mhs11 maka datanya
otomatis akan terhapus
Delete to delete
|
CREATE TABLE MHS13 (
NPM char(10)
primary key,
nama
varchar(25),
no_hp
varchar(12)
)
go
CREATE TABLE MHS14 (
NPM char(10)
primary key,
nama
varchar(25),
no_hp
varchar(12)
)
go
create trigger tr_mhs7 on MHS13
for delete
as
begin
declare @NPM char(10)
declare @nama varchar(25)
declare @no_hp varchar(12)
select @NPM= NPM from deleted
select @nama= nama from deleted
select @no_hp= no_hp from deleted
delete from mhs14 where npm=@npm
end
insert into MHS14 values ('1134010061', 'pradhita', '085648182230')
insert into MHS14 values ('1134010062', 'yudha', '085648182231')
insert into MHS14 values ('1134010063', 'negara', '085648182232')
delete from mhs13 where npm='1134010061'
select* from MHS14
|
Sebelum di delete data mhs13 dan mhs14 sama
Setelah di delete data yg sama pada kedua table
dihapus
Delete to update
|
CREATE TABLE MHS15 (
NPM char(10)
primary key,
nama
varchar(25),
no_hp
varchar(12)
)
go
CREATE TABLE MHS16 (
NPM char(10)
primary key,
nama
varchar(25),
no_hp
varchar(12)
)
go
create trigger tr_mhs8 on MHS15
for delete
as
begin
declare @NPM char(10)
declare @nama varchar(25)
declare @no_hp varchar(12)
select @NPM= NPM from deleted
select @nama= nama from deleted
select @no_hp= no_hp from deleted
update mhs16 set nama=@nama, no_hp=@no_hp where npm=@npm
end
insert into MHS15 values ('1134010061', 'arivandi', '085648182230')
insert into MHS15 values ('1134010062', 'fauzan', '085647987631')
insert into MHS15 values ('1134010063', 'rizal', '085648198762')
delete from mhs15 where npm='1134010062'
|
Data di table mhs15 sebelum di delete
Data table mhs16 sebelum dieksekusi
Data table mhs16 setelah dieksekusi
Delete to insert
|
CREATE TABLE MHS17 (
NPM char(10)
primary key,
nama
varchar(25),
no_hp
varchar(12)
)
go
CREATE TABLE MHS18 (
NPM char(10)
primary key,
nama
varchar(25),
no_hp
varchar(12)
)
go
create trigger tr_mhs9 on MHS17
for delete
as
begin
declare @NPM char(10)
declare @nama varchar(25)
declare @no_hp varchar(12)
select @NPM= NPM from deleted
select @nama= nama from deleted
select @no_hp= no_hp from deleted
insert into mhs18 values (@NPM, @nama, @no_hp)
end
insert into MHS17 values ('1134010061', 'arivandi', '085648182230')
insert into MHS17 values ('1134010062', 'fauzan', '085647987631')
insert into MHS17 values ('1134010063', 'rizal', '085648198762')
delete from mhs17 where npm='1134010062'
select* from MHS18
|
Data di mhs17 sebelum dihapus dan data di mhs18
masih kosong
Data di mhs17 setelah dihapus
Data di table mhs18 terisi data yang telah dihapus
0 comments:
Post a Comment