نوشته شده توسط
s_mokhtari
با سلام و تشكر از راهنمايي شما
دوست عزيز من با تريگر آشنايي اصلان ندارم ميشه يه نمونههايي از ساده به سخت بهم نشون بدي
با تشكر
سلام اینجا چند تا نمونه از ایجاد و استفاده از تریگرها گذاشتم البته با اجازه از استاد گرامی جناب مهندس ثباتی
---- TRIGGERS -----
go
create trigger tr1 on customers
after insert
as
rollback tran
go
create trigger tr1 on customers
after insert
as
print 'You cannot register any new customer!'
rollback tran
go
insert Customers(CustomerID,CompanyName) values('a21','b2')
go
create trigger tr2 on orders
after insert
as
select * from inserted
rollback tran
go
insert Orders(CustomerID, OrderDate) values('vinet',GETDATE())
go
alter trigger tr2 on orders
after insert
as
declare @cid char(5)
select @cid=customerid from inserted
if (select count(orderid) from orders where customerid=@cid)<=5
rollback tran
go
alter trigger tr2 on orders
after insert
as
if (select count(o.orderid) from inserted i join orders o
on i.customerid=o.customerid)<=5
rollback tran
go
insert Orders(CustomerID, OrderDate) values('alfki',GETDATE())
insert Orders(CustomerID, OrderDate) values('anatr',GETDATE())
select * from Orders
order by CustomerID
delete orders where OrderID=11081
---------------------------
go
create trigger tr3 on [order details]
after insert
as
declare @pid int
declare @pc int
select @pid=productid, @pc=Quantity from inserted
if (select UnitsInStock from products where productid=@pid)<@pc
rollback tran
go
insert [Order Details] (OrderID,ProductID,Quantity) values(10248,1,39)
go
Alter trigger tr3 on [order details]
After insert
as
If Not Exists (Select *
From Inserted i Join dbo.Products p
on i.ProductID = p.ProductID
where i.Quantity <= p.UnitsInStock )
Begin
print 'mojodi dar Anbar kafi nist'
Rollback
end
Go
insert [Order Details] (OrderID,ProductID,Quantity) values(10249,1,9)
---------------------------------------
go
create trigger tr7 on customers2
after delete
as
declare @cid nchar(5)
select @cid=customerid from deleted
if (select count(orderid) from orders where customerid=@cid)>5
rollback tran
g
go
alter trigger t2 on customers2
after delete
as
if(select count(o.orderid) from orders o
join deleted d on o.customerid=d.customerid) >5
rollback tran
go
-----------------------------------
go
create trigger tr4 on customers
instead of insert
as
select 1
go
insert Customers(CustomerID,CompanyName) values('a3','b3')
select * from Customers
alter trigger tr4 on customers
instead of insert
as
insert customers select * from inserted
go
-----------
alter trigger tr5 on [order details]
instead of insert
as
declare @cnt int
--declare @qnt int
set @cnt = (select p.UnitsInStock from inserted i
inner join products p
on i.productid=p.productid)
if @cnt < (select quantity from inserted)
begin
insert [Order Details] (ProductID,Orderid,Quantity) (select ProductID,orderid,@cnt from inserted)
print 'meghdare mojodi insert shod'
end
else
insert [Order Details] (ProductID,Orderid,Quantity) (select productid, orderid,quantity from inserted)
go
Create trigger tr3 on [order details]
instead of insert
as
If Exists (Select *
From Inserted i Join dbo.Products p
on i.ProductID = p.ProductID
where i.Quantity <= p.UnitsInStock )
insert into [order details] select * from inserted
else
Begin
insert into [order details]
Select i.orderId,i.productid,i.unitprice,p.UnitsInStock ,i.discount
from inserted i join products p
on i.productid = p.productid
-- print 'mojodi dar Anbar kafi nist'
-- Rollback
end
--
go
Create trigger tr3 on [order details]
instead of insert
as
insert [order details] Select i.orderId,i.productid,i.unitprice,
case when i.quantity>p.unitsinstock then p.unitsinstock
else i.quantity end ,i.discount
from inserted i join products p
on i.productid = p.productid
insert [Order Details] (OrderID,ProductID,Quantity) values(10300,1,40)
insert [Order Details] (OrderID,ProductID,Quantity) values(10301,1,4)
select * from [order details]
where orderid=10301