Trigerat – “Sensoret”
Cfare jane triggerat
Si funksionon nje INSERT trigger (sensor per Insert)
Si funksionon nje DELETE trigger (sensor per delete)
Si funksionon nje update trigger (sensor per modifikim)
Cfare jane triggerat
Procedura qe ekzektuohen automatikisht nese ndodh nje kompande insert,update ose delete ne nje tabele
Triggerat dhe komanda qe i aktivizoi ato jane pjese e e te njejtit transaksion
Si punon nje Insert trigger
Kur aktivizoeht nje INSERT trigger, rreshtat e reja shtohen edhe ne tabelen INSERTED
Tabela inserted eshte nje tabele llogjike qe mban nje kopje et rreshtave te saposhtuar.
Triggeri mund te lexoje te dhenat ne tabelen inserted per te vendosur cfare duhet te beje
Si krijohet nje INSERT trigger
CREATE TRIGGER NewCats on Categories
AFTER INSERT AS
BEGIN
select CategoryId,CategoryName from inserted
END
Si funksionon nje delete trigger
Rreshtat e fshire vendosen ne nje tabele me emrin Deleted
Tabela Deleted eshte nje tabele llogjike qe permban rreshtat e sapofshire
Si krijohet nje DELETE Trigger
CREATE TRIGGER MosFshiKat on Categories
AFTER DELETE AS
BEGIN
ROLLBACK
END
Trigeri i mesiperm nuk lejon fshirjen ne tabelen categories
Si funksionon nje update trigger
Sensori per modifikim bazohet ne te dy tabelat llogjike:
Tabela DELETED ruan versionin e rreshtave te modifikuar para modifkimit
Tabela INSERTED ruan versionin e rreshtave te modifikuar pas modifkimit
Si krijohet nje UPDATE Trigger
CREATE TRIGGER AfishoKatMod on Categories
AFTER UPDATE AS
BEGIN
SELECT CategoryId, CategoryName from Categories
END
Shembull me update
create trigger employeeslog
on employees
after update
as
begin
select deleted.EmployeeID,deleted.FirstName,
deleted.LastName,deleted.Title as OldPosition,
inserted.Title as NewPosition,
CURRENT_USER as ModifiedBy,
GETDATE() as ModifiedOn
from inserted inner join deleted
on inserted.EmployeeID=deleted.EmployeeID
end
Shembuj
Shembull 1
-- Nje insert trigger qe ben nje rollback
-- trigger therret nje funksion te krijuar parprakisht
-- trigeri shtimin e nje shkrimtari nese per te nuk ka libra te regjistruar parprakisht
CREATE TRIGGER NewAuthor2 on Authors
AFTER INSERT AS
BEGIN
Declare @auid as int
select @auid=inserted.AuthorId from insterted
if dbo.librat(@auid)=0
BEGIN
RAISERROR('shkrimtari nuk gjendet ne tabelen botime',10,1)
ROLLBACK
END
END
Shembull 2
-- Nje triger per delete qe kryen nje delete ne cascade te detajve te porosise kur fshihet nje porosi
-- trigeri nenkupton qe nuk ka kufuzim celes te jashtem midis dy tabelave
CREATE TRIGGER DeletePorosi on Orders
AFTER DELETE AS
BEGIN
Declare @orderid as int
select @orderid=deleted.OrderNo from deleted
delete from OrderDetails where OrderNo=@orderid
END
Shembull 3
--triger qe kontrollon nese autori ka botime dhe nuk lejon fshirjen
--transaksioni kthen mbarpsht te gjithe inserted ndonese autori me kodin 1003 nuk ka
-- botime
CREATE TRIGGER DeleteAuthor on Authors
AFTER DELETE AS
BEGIN
if exists(
select AuthorId from deleted where AuthorId in
(
select kodi from botime
)
)
begin
raiserror('nuk mund te fshihet autori pasi ka botime',1,10)
if (@@TRANCOUNT>0)
begin
PRINT 'transaksioni anullohet'
ROLLBACK
end
end
END
Shembull 4
--triger qe ruan historikun e veprimeve te modifikimit mbi tabelen authors
-- Se pari do te krijojme tabelat ku do te ruajme historikun e veprimeve
--po krijojme tabelen eventlog
CREATE TABLE [dbo].[eventlog](
[kodi] [int] IDENTITY(1,1) NOT NULL,
[event] [varchar](250) NOT NULL,
[eventtime] [datetime] NULL,
CONSTRAINT [PK_eventlog] PRIMARY KEY CLUSTERED
(
[kodi] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-- te krijojme tabellen ku do te ruhen rreshtat e modifikuar te tabeles Authors
CREATE TABLE [dbo].[AuthorsHistory](
[AuthorId] [int] NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[Lastname] [varchar](50) NOT NULL,
[YearBorn] [int] NULL,
[YearDied] [int] NULL,
[Note] [varchar](250) NULL,
lastchange datetime NULL,
changedby varchar(30)
CONSTRAINT [PK_Authors2] PRIMARY KEY CLUSTERED
(
[AuthorId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-- Se fundi krijojme triggerin qe do te ruaje historikun e veprimeve te modifikimit mbi tabelen Authors
create trigger NdryshoAuthor on Authors
after update
as
BEGIN
--ruaj eventin
insert into eventlog(event)
select 'authors are updated: old data-kodi='+convert(varchar,AuthorID,100)
+' FN='+Firstname+' LN='+LastName
from deleted
--ruaj event
insert into eventlog(event)
select 'new value for updated authors : kodi='+convert(varchar,AuthorID,100)
+' FN='+Firstname+' LN='+LastName
from inserted
--ruaj historikun e rreshtit
insert into authorshistory(AuthorId,FirstName,Lastname,changedby)
select AuthorId,FirstName,Lastname,CURRENT_USER from deleted
END
SHEMBUJ
CREATE TRIGGER ChangeCat ON CATEGORIES
AFTER UPDATE
AS
BEGIN
print 'rreshtat e kategorive para modifikimit'
SELECT CategoryId,CategoryName from deleted
print 'rreshtat e kategorive pas modifikimit'
SELECT CategoryId,CategoryName from inserted
END
CREATE TRIGGER [dbo].[NewCats] on [dbo].[Categories]
AFTER INSERT AS
BEGIN
insert INTO NewCats1(CategoryId,CategoryName)
select CategoryId,CategoryName from inserted
END
CREATE TRIGGER CategoriesRecBin
ON CATEGORIES
AFTER DELETE
AS
BEGIN
INSERT INTO DeletedCats(CategoryId,CategoryName)
SELECT CategoryId,CategoryName FROM DELETED
END
--le te provojme rezultatin
insert into Categories (CategoryID,CategoryName)
values
(220,'prove220'),
(221,'prove221'),
(222,'prove222'),
(223,'prove223')
select * from newcategories
-- trigger qe nuk lejon fshirjen e kategorive te produkteve
CREATE TRIGGER nodeletecat
ON Categories
AFTER DELETE
AS
BEGIN
print 'nuk fshihet cat'
ROLLBACK
END
Ushtrim
Krijoni nje triger qe ruan nje kopje te cdo kategorie te shtuar
--paraprakish kemi ndertuar nje tabele me emrin
Operations(OpId IDENTITY(1,1),OperationType,ObjectName,ModifiedValues)
-- te ruajme psh u shtua nje kategori e re
--OperationType=INSERT,Object=categories,ModifiedValues="CategoryId=10 CategoryName=prove"
CREATE TRIGGER newkategori
ON CATEGORIES
AFTER INSERT
AS
BEGIN
INSERT into operations (OperationType,ObjectName,ModifiedValues)
select 'INSERT','Categories',
'CategoryId='+convert(varchar,CategoryId)+' CategoryName='+CategoryName
from INSERTED
END
-- te ruajme veprimin insert
--ruhen edhe vlerat e vjetra edhe vlerat e reja
-- tabela operations ka nje kollone optime me vler default getdate()
CREATE TRIGGER udpatekategori
ON CATEGORIES
AFTER UPDATE
AS
BEGIN
INSERT into operations (OperationType,ObjectName,ModifiedValues)
select
'DELETE','Categories',
'CategoryId='
+convert(varchar,CategoryId)+' CategoryName='+CategoryName
from DELETED
INSERT into operations (OperationType,ObjectName,ModifiedValues)
select
'INSERT','Categories',
'CategoryId='
+convert(varchar,CategoryId)+' CategoryName='+CategoryName
from INSERTED
END
CREATE TRIGGER porosiere
ON Orders
INSTEAD OF INSERT
AS
BEGIN
select * from Inserted
END
CREATE TRIGGER fshikategori
ON Categories
INSTEAD OF DELETE
AS
BEGIN
INSERT INTO Operations (OperationType,ObjectName,ModifiedValues)
SELECT 'DELETE','Categories','CategoryId='+convert(varchar,CategoryId)+' CategoryName='+CategoryName
FROM DELETED
END
shembujt e dates 10 maj 2012
-- te ndertojme nje triger
--qe nuk lejon fshirjen e zonjushave
-- nga tabela punonjes
select * from Employees
CREATE TRIGGER fshipunonjes
ON Employees
AFTER DELETE
AS
BEGIN
IF EXISTS
(
SELECT * FROM deleted
where TitleOfCourtesy='Ms.'
or TitleOfCourtesy='Mrs.'
)
ROLLBACK
END
-- Te shtojme nje punonjes
-- me titullin 'Ms.' dhe do tentojme me
--pas ta fshijme
INSERT INTO
Employees(FirstName,LastName,TitleOfCourtesy)
values ('Leke','Plepi','Mr.')
delete from Employees
where FirstName='Leke'
select * from Employees
order by EmployeeID desc
-- trigerat instead of
insert into Products(ProductName,UnitsInStock)
values ('Makina Ducati',100)
-- nese tentojme te fshijme nje produkt
-- db nuk duhet ta fshije por ta modifikoje ate
create trigger fshiprod
on Products
instead of delete
as
begin
update Products
set UnitsInStock=0
where ProductId in
(select
ProductID from deleted)
end
delete Products
where ProductName='Makina Ducati'
select * from Products
order by ProductID desc
-- te ndertojme nje triger i cili nuk
--lejon te shtohen porosi me destinacion
--shtetin USA
create trigger stopusa
on Orders
after insert
as
begin
if exists
(
select * from inserted
where ShipCountry='USA'
)
ROLLBACK
end
insert into
Orders(EmployeeID,CustomerID,ShipCountry)
values (1,'ALFKI','USA')
-- a mund te kapim tabela te tjera nga brenda trigerit?
-- ta provojme
-- Nuk lejohet te shesim me tek nje klient nese xhiroja
--financiare qe ai ka realizuar eshte me e madhe se 1000 euro
create trigger debitore
on Orders
after insert
as
begin
IF exists
(
select * from inserted
where dbo.CustomerTurnOver(CustomerID)>1000
)
rollback
end
--- duam qe te ruajme nje kopje te daljeve nga magazina
-- nenkptojne update te tabeles products tek kollona unitsinstock
-- cfare informacionesh duam te ruajme?
-- kush e beri daljen?
-- kur e beri daljen
--kodi i produktit
-- emri i produktit
-- sa ishte gjendja
-- sa u bene dalje
-- sa eshte gjendje aktualisht?
-- te ndertojme nje tabele me keto te dhena
print CURRENT_USER
-- afishon userin qe eshtre loguar ne kete moment ne sistem
CREATE TRIGGER log_dalje_hyrje
ON Products
AFTER UPDATE
as
BEGIN
insert into logmagazina
(ProductId,ProductName,GjendjaPara,GjendjaPas,Dalje)
select ins.ProductID,ins.ProductName,
del.UnitsInStock,ins.UnitsInStock,
del.UnitsInStock-ins.UnitsInStock
from
inserted ins inner join deleted del
on ins.ProductID=del.ProductID
END
update Products
set UnitsInStock=UnitsInStock-10
where ProductID=1
-- a u modifikua? PO
select * from Products
-- a u ruajt dalja nga magazina ne log????
select * from logmagazina
-- a mund te therras nje funksion nga brenda trigerit?
-- te bej nje funksion qe merr kodin e nje klienti dhe
-- kthen xhiron e tij financiare
create function CustomerTurnOver
(
@cid as char(5)
)
returns money
as
begin
declare @xhiro as money
select @xhiro=SUM(UnitPrice*Quantity)
from
Orders o inner join [Order Details] od
on o.OrderID=od.OrderID
where CustomerID=@cid
group by CustomerID
return @xhiro
end
print dbo.CustomerTurnOver('ALFKI')