Notes

--Trigger oluşturma

--İnsert trigger

create trigger HesapEklemeDurumu
on Hesap
after insert --trigger tabloya giriş işlemi yapıldıktan sonra
as
begin
set nocount on
select 'yeni kayıt ekledi'
set nocount off
end


insert into Hesap(Adi,Soyad,Bolum,Fiyat)
values('Ali','Tek',1,500)


create trigger trg_PersonelH
on Personeller
after insert
as
raiserror ('Eklenen personeli bildir' ,16,10)

insert into Hesap(Adi,Soyad,Bolum,Fiyat)
values('Ali','Tek',1,500)

INSERT INTO [dbo].[Personeller]
([PpersonelID]
,[ADI]
,[SOYADI]
,[MESLEK])
VALUES
(10,'ali','veli','issiz')


--girilen tablo değerini alıp başka bir tabloya eş zamanlı yazma

create table HesapLog
([PersonelID] int
,[Adi]



) --eksik

--after insert -->

create trigger trg_GetProduct
on Production.Product
after insert
as
begin

select PP.ProductID,PP.Name,PP.ProductNumber from Production.Product
as PP inner join inserted as I on PP.ProductID=I.ProductID

end

insert into Production.Product (Name,ProductNumber,MakeFlag ,FinishedGoodsFlag,ReorderPoint,
StandardCost,ListPrice,DaysToManufacture,SellStartDate,rowguid,ModifiedDate)
values('Smart Kit','SM-301',1,0,50,700,0.00,0.00,3,GETDATE(),NEWID(),GETDATE());

SELECT * FROM Production.Product where ProductNumber='Smart Kit'

create trigger trtr_KullaniciSil
on Personeller
after delete --silinme işlemi yapılınca
as
begin

select deleted.FirstName+' '+deleted.LastName+' kişisi silindi') as SilinenKisi
from deleted --silinen geçici tablo üzerinden işlem yaptık
end

select * from dbo.Personeller

delete from Personeller where BusinessEntityID=265

--Update trigger

create trigger trgr_Guncelle on Production.Product
after update as begin
Update Production.Product set ModifiedDate=GETDATE() where ProductID=(select ProductID from inserted)

end

select * from Production.Product where ProductID=2

select * from Production.Product



create table GuncellenenUrunler(
ProductID int,
Name varchar(50),
ProductNumber nvarchar(25),
ListPrice money,
ModifiedDate datetime
)

create trigger trgr_guncellenenUrunLog
on Production.Product
after update
as
begin

--değişkenler
declare
@ProductID int,
@Name varchar (50),
@ProductNumber nvarchar(25),
@ListPrice money,
@ModifiedDate datetime

--değişkenlere değer atama
select
@ProductID=ins.ProducutID,
@Name=ins.Name,
@ProductNumber=ins.ProductNumber,
@ListPrice=ins.ListPrice,
@ModifiedDate =ins.ModifiedDate

from inserted as ins
--GuncellenenUrunler veri girişi yapıldı
insert into GuncellenenUrunler
values(@ProductID,@Name,@ProductNumber,@ListPrice,@ModifiedDate )

end

update Production.Product
set Name='Decal one' where ProductID=325

select * from Production.Product

select*from GuncellenenUrunler

truncate table GuncellenenUrunler



create table SilinenUrunler(
ProductID int,
Name varchar(50),
ProductNumber nvarchar(25),
ListPrice money,
ModifiedDate datetime
)

create trigger trgr_silinenUrunLog
on Production.Product
after update
as
begin

--değişkenler
declare
@ProductID int,
@Name varchar (50),
@ProductNumber nvarchar(25),
@ListPrice money,
@ModifiedDate datetime

--değişkenlere değer atama
select
@ProductID=ins.ProductID,
@Name=ins.Name,
@ProductNumber=ins.ProductNumber,
@ListPrice=ins.ListPrice,
@ModifiedDate =ins.ModifiedDate

from deleted as ins
--GuncellenenUrunler veri girişi yapıldı
insert into SilinenUrunler
values(@ProductID,@Name,@ProductNumber,@ListPrice,@ModifiedDate )

end

delete from Production.Product where ProductID=325

select *from SilinenUrunler



---Birden fazla işlem için Trigger oluşturmak

create trigger trgr_Tumu
on Hesap
after insert,update,delete




create table Siparis2(

SiparisID int identity not null primary key,
MusteriID nvarchar(50) not null,
SiparisTarih datetime not null
)

create table Urunler2(

UrunID int identity not null primary key,
Ad nvarchar(50),
BirimFiyat money not null

)

create table SiparisUrunler2(

SiparisID int not null references Siparis2(SiparisID),
UrunID int not null references Urunler2(UrunID),
BirimFiyat money not null,
Miktar int not null

constraint PKSiparisUrun2 primary key clustered(SiparisID,UrunID) --İki sütun içinde clustered index oluşturuyoruz.
)

create table Musteri1(

MusteriID int not null primary key,
Ad varchar(40) not null
)

insert into Musteri1 values(1,'mobilveri bilişim')
insert into Musteri1 values(2,'Smartpro bilgisayar')

insert into Siparis2 values (1,GETDATE());
insert into Siparis2 values (2,GETDATE());

insert into Urunler2 values ('Developer Book',150)
insert into Urunler2 values ('Frontend Book',100)

insert into SiparisUrunler2 values (1,1,150,3)
insert into SiparisUrunler2 values (2,2,100,2)


select* from Siparis2
select* from Urunler2
select* from SiparisUrunler2
select* from Musteri1


go