select * from employees
alter table employees
add DriverNo varchar(15) null
alter trigger MakeUniqueDriverNo
on employees
instead of insert , update
as
declare @driverno varchar(15)
declare @employeeID int
declare @newDriverno int
select @driverno=driverno ,@employeeID=employeeID from inserted
set @newDriverno =(select convert(int,max(driverno)) + 1 from employees)
if update(driverno)
begin
if exists ( select top 1 employeeID from employees
where driverno = @driverno)
begin
update employees
set driverno = convert(varchar(15),@newDriverno)
where employeeID = @employeeID
select ‘Driverno değiştirilerek kaydedildi…’
end
else
begin
update employees
set driverno = @driverno
where employeeID = @employeeID
select ‘Driverno değiştirilmeden kaydedildi…’
end
end
—- test
update employees
set driverno = ‘12343’
where employeeID = 7
select driverno from employees
create table TriggerTest
(
testID int not null primary key identity(1,1),
testAdi varchar(50) null,
testAciklama varchar(100) null
)
declare @counter int
set @counter = 1
declare @testAdi varchar(50)
declare @testAciklama varchar(100)
set @testAdi = ‘test ‘
set @testAciklama = ‘Açıklama ‘
while @counter <= 20
begin
set @testAdi = @testAdi + convert(varchar(10),@counter)
set @testAciklama = @testAciklama + convert(varchar(10),@counter)
insert into TriggerTest
values(@testAdi,@testAciklama)
set @counter = @counter + 1
end
select * from TriggerTest
create trigger deleteOnce
on TriggerTest
for delete
as
if(select count(*) from deleted ) > 1
begin
raiserror(‘Aynı anda birden fazla kayıt silemezsiniz’,16,1)
rollback tran
end
delete from TriggerTest
where testID > 1
create table urunler
(
UrunID int not null identity(1,1) primary key,
UrunAdi varchar(50) not null,
Miktar int not null default(0)
)
create table MusteriSiparisleri
(
SiparisID int not null identity(1,1) primary key,
UrunID int not null,
Miktar int not null
)
create table SirketSiparisleri
(
SiparisID int not null identity(1,1) primary key,
UrunID int not null,
Miktar int not null
)
declare @urunAdi varchar(50)
declare @miktar int
declare @counter int
set @counter = 1
while @counter <= 20
begin
set @urunAdi = ‘Ürün ‘ + convert(varchar(10),@counter)
set @miktar = 10 * @counter
insert MusteriSiparisleri
values (@urunAdi , @miktar)
set @counter = @counter +1
end
select * from urunler
create trigger SiparisKontrol
on MusteriSiparisleri
for insert , update
as
declare @girilenUrunID int
declare @girilenMiktar int
declare @miktarFark int
select @girilenUrunID = UrunID , @girilenMiktar=Miktar
from inserted
if (select Miktar from Urunler where UrunID = @girilenUrunID) < @girilenMiktar
begin
set @miktarFark = @girilenMiktar – (select Miktar from Urunler where UrunID = @girilenUrunID)
update urunler
set miktar = 0
where UrunID = @girilenUrunID
insert SirketSiparisleri
values (@girilenUrunID,@miktarFark)
end
else
begin
update urunler
set miktar = miktar – @girilenMiktar
where UrunID = @girilenUrunID
end
select * from urunler
select * from MusteriSiparisleri
select * from SirketSiparisleri
insert MusteriSiparisleri
values (10,100)
insert MusteriSiparisleri
values (8,100)
—————————————————–
create table Kullanicilar
(
KullanaciID int not null identity(1,1) primary key,
AdSoyad varchar(100) not null,
KullaniciTipi tinyint not null default(2)
— 1 : admin ; 2: Normal
)
insert Kullanicilar
values (‘Hasan Mansur’ , 1)
insert Kullanicilar
values (‘Nalan Arıcı’ , 2)
insert Kullanicilar
values (‘Mehmet Çakmak’ , 1)
insert Kullanicilar
values (‘Özgür Gül’ , 2)
insert Kullanicilar
values (‘Murat Karakuş’ , 1)
insert Kullanicilar
values (‘Aylin Aydın’ , 2)
insert Kullanicilar
values (‘Murat Çakıcı’ , 1)
select * from Kullanicilar
truncate table Kullanicilar
create table Admin
(
KullaniciID int not null primary key,
Durum tinyint not null default(1) — 0: silindi ; 1:Aktif
)
alter trigger AdminLog
on Kullanicilar
for insert , update
as
declare @inserted_AdSoyad varchar(100)
declare @inserted_KullaniciTipi tinyint
declare @yeni_KullaniciID int
declare @updated_KullaniciID int
select @inserted_AdSoyad = AdSoyad
,@inserted_KullaniciTipi = KullaniciTipi
from inserted
——————- update ———————–
if update (KullaniciTipi)
begin
set @updated_KullaniciID = (select KullanaciID from inserted)
if @inserted_KullaniciTipi = 1 — yeni kullanicitipi admin ise
begin
— eğer bu kullanıcı admin tablosunda yoksa , ekle
if not exists (select top 1 KullaniciID
from Admin where KullaniciID = @updated_KullaniciID)
begin
insert Admin values(@updated_KullaniciID,1)
print ‘Kayıt admine eklendi…’
end
end
else — eğer yeni kullanıcı tipi = 2 yani normal kullanıcı ise
begin
— eğer bu kullanıcı adminde varsa , sil
if exists (select top 1 KullaniciID
from Admin where KullaniciID = @updated_KullaniciID)
begin
delete from Admin
where KullaniciID = @updated_KullaniciID
print ‘Kayıt adminden silindi…’
end
end
end
————————— insert ————————-
else
begin
if @inserted_KullaniciTipi = 1
begin
select @yeni_KullaniciID = @@identity
insert Admin values(@yeni_KullaniciID,1)
print ‘KullanıcıTipi Admine yazildi’
end
else
print ‘Kullanıcı normal tiptedir.Admine yazilmadi’
end
select * from admin
select * from kullanicilar
update kullanicilar
set KullaniciTipi = 2
where KullanaciID = 2
insert kullanicilar values(‘Murat BeyazKuş’,1)
create table Silinenler
(
KullanaciID int not null primary key,
AdSoyad varchar(100) not null,
KullaniciTipi tinyint not null default(2) ,
— 1 : admin ; 2: Normal
IslemTarihi datetime not null default(getdate())
)
alter trigger SilveLogla
on Kullanicilar
instead of delete
as
declare @KullanaciID int ,@AdSoyad varchar(100),
@KullaniciTipi tinyint
select @KullanaciID=KullanaciID
,@AdSoyad=AdSoyad
,@KullaniciTipi = KullaniciTipi
from deleted
— 2 iş var
–1 silinenlere yaz
–2 kullanicilar dan sil
–3 bu @KullanaciID adminde varsa sil
–1
insert silinenler
values(@KullanaciID,@AdSoyad,@KullaniciTipi,getdate())
print cast(@KullanaciID as varchar(10)) + ‘ nolu ‘
+ @AdSoyad +’ adlı kayıt silinenler tablosuna yazıldı.’
–2
delete from kullanicilar
where KullanaciID = @KullanaciID
print cast(@KullanaciID as varchar(10)) + ‘ nolu ‘
+ @AdSoyad +’ adlı kayıt kullanicilar tablosundan silindi.’
–3
if exists ( select top 1 KullaniciID
from Admin where KullaniciID = @KullanaciID)
begin
delete from admin
where KullaniciID = @KullanaciID
print cast(@KullanaciID as varchar(10)) + ‘ nolu ‘
+ @AdSoyad +’ adlı kayıt admin tablosundan silindi.’
end
select * from silinenler
select * from admin
select * from kullanicilar
delete from kullanicilar
where KullanaciID in (2,3)
Create Trigger ile otomatik kayıt girişi
Hiç yorum yok:
Yorum Gönder