Yeni Kayıt
Konudaki Resimler
|
Hızlı 
Bildirim
---Trigger-- yardım Lütfenn..Çok Acill
Sıcak Fırsatlarda Tıklananlar
Editörün Seçtiği Fırsatlar
Daha Fazla 
Bu Konudaki Kullanıcılar:
Daha Az 
1 Misafir - 1 Masaüstü
Giriş
Mesaj
-
-
derste yaptığımız stok kontrol programının bir kısmı olan trigger örneği
umarım işine yarar
create trigger satıs_islemi on dbo.satıs after insert
as
declare @satilan_urunid bigint, @satılan_adet int
select @satilan_urunid=urunid,@satılan_adet=adet from inserted
update urun set adet=adet-@satılan_adet where id=@satilan_urunid
create trigger giris_islemi on dbo.urun_giris after insert
as
declare @girilen_urunid bigint,@girilen_adet int
select @girilen_urunid=urunid,@girilen_adet=adet from inserted
update urun set adet=adet+@girilen_adet where id=@girilen_urunid -
MSDN'den yararlanmanı öneririm. -
asagida verdigim örnekler zamanim olmamasindan dolayi alintiddir.insalla vaktim
olursa ve gerekirse kendimizde yazariz yani ;)
***********************************************************
Insert trigger example
When you insert a new foreign key row, make sure the foreign key matches a primary key.
The trigger should check for joins between the inserted rows (using the inserted table) and
the rows in the primary key table, and then roll back any inserts of foreign keys that do not
match a key in the primary key table.
The following trigger compares the title_id values from the inserted table with those from
the titles table. It assumes that you are making an entry for the foreign key and that you
are not inserting a null value. If the join fails, the transaction is rolled back.
create trigger forinsertrig1
on salesdetail
for insert
as
if (select count(*)
from titles, inserted
where titles.title_id = inserted.title_id) !=
@@rowcount
/* Cancel the insert and print a message.*/
begin
rollback transaction
print "No, the title_id does not exist in
titles."
end
/* Otherwise, allow it. */
else
print "Added! All title_id's exist in titles."
@@rowcount refers to the number of rows added to the salesdetail table. This is also the number
of rows added to the inserted table. The trigger joins titles and inserted to determine whether all
the title_ids added to salesdetail exist in the titles table. If the number of joined rows, which is
determined by the select count(*) query, differs from @@rowcount, then one or more of the
inserts is incorrect, and the transaction is canceled.
This trigger prints one message if the insert is rolled back and another if it is accepted.
To test for the first condition, try this insert statement:
insert salesdetail
values ("7066", "234517", "TC9999", 70, 45)
To test for the second condition, enter:
insert salesdetail
values ("7896", "234518", "TC3218", 75, 80)
*************************************************************************************
Delete trigger examples
When you delete a primary key row, delete corresponding foreign key rows in dependent
tables. This preserves referential integrity by ensuring that detail rows are removed when
their master row is deleted. If you do not delete the corresponding rows in the dependent
tables, you may end up with a database with detail rows that cannot be retrieved or
identified. To properly delete the dependent foreign key rows, use a trigger that performs
a cascading delete.
Cascading delete example
When a delete statement on titles is executed, one or more rows leave the titles table and
are added to deleted. A trigger can check the dependent tables--titleauthor, salesdetail,
and roysched--to see if they have any rows with a title_id that matches the title_ids removed
from titles and is now stored in the deleted table. If the trigger finds any such rows,
it removes them.
create trigger delcascadetrig
on titles
for delete
as
delete titleauthor
from titleauthor, deleted
where titleauthor.title_id = deleted.title_id
/* Remove titleauthor rows that match deleted
** (titles) rows.*/
delete salesdetail
from salesdetail, deleted
where salesdetail.title_id = deleted.title_id
/* Remove salesdetail rows that match deleted
** (titles) rows.*/
delete roysched
from roysched, deleted
where roysched.title_id = deleted.title_id
/* Remove roysched rows that match deleted
** (titles) rows.*/
Restricted delete examples
In practice, you may want to keep some of the detail rows, either for historical purposes
(to check how many sales were made on discontinued titles while they were active) or
because transactions on the detail rows are not yet complete. A well-written trigger should
take these factors into consideration.
Preventing primary key deletions
The deltitle trigger supplied with pubs2 prevents the deletion of a primary key if there are
any detail rows for that key in the salesdetail table. This trigger preserves the ability to
retrieve rows from salesdetail:
create trigger deltitle
on titles
for delete
as
if (select count(*)
from deleted, salesdetail
where salesdetail.title_id =
deleted.title_id) > 0
begin
rollback transaction
print "You cannot delete a title with sales."
end
In this trigger, the row or rows deleted from titles are tested by being joined with the salesdetail
table. If a join is found, the transaction is canceled.
Similarly, the following restricted delete prevents deletes if the primary table, titles, has
dependent children in titleauthor. Instead of counting the rows from deleted and titleauthor,
it checks to see if title_id was deleted. This method is more efficient for performance reasons
because it checks for the existence of a particular row rather than going through the entire
table and counting all the rows.
Recording errors that occur
The next example uses raiserror for error message 35003. raiserror sets a system flag to record
that the error occurred. Before trying this example, add error message 35003 to the
sysusermessages system table:
sp_addmessage 35003, "restrict_dtrig - delete failed: row exists in titleauthor for this title_id."
The trigger is:
create trigger restrict_dtrig
on titles
for delete as
if exists (select * from titleauthor, deleted where
titleauthor.title_id = deleted.title_id)
begin
rollback transaction
raiserror 35003
return
end
To test this trigger, try this delete statement:
delete titles
where title_id = "PS2091"
**************************************************************************************
Update trigger examples
The following example cascades an update from the primary table titles to the dependent tables
titleauthor and roysched.
create trigger cascade_utrig
on titles
for update as
if update(title_id)
begin
update titleauthor
set title_id = inserted.title_id
from titleauthor, deleted, inserted
where deleted.title_id = titleauthor.title_id
update roysched
set title_id = inserted.title_id
from roysched, deleted, inserted
where deleted.title_id = roysched.title_id
update salesdetail
set title_id = inserted.title_id
from salesdetail, deleted, inserted
where deleted.title_id = salesdetail.title_id
end
To test this trigger, suppose that the book Secrets of Silicon Valley was reclassified to a
psychology book from popular_comp. The following query updates the title_id PC8888 to
PS8888 in titleauthor, roysched, and titles.
update titles
set title_id = "PS8888"
where title_id = "PC8888"
Restricted update triggers
A primary key is the unique identifier for its row and for foreign key rows in other tables.
Generally, you should not allow updates to primary keys. An attempt to update a primary key should be taken very seriously. In this case, you need to protect referential integrity by rolling back
the update unless specified conditions are met.
Sybase suggests that you prohibit any editing changes to a primary key, for example by
revoking all permissions on that column. However, if you want to prohibit updates only
under certain circumstances, use a trigger.
Restricted update trigger using date functions
The following trigger prevents updates to titles.title_id on the weekend. The if update clause
in stopupdatetrig allows you to focus on a particular column, titles.title_id. Modifications to the
data in that column cause the trigger to go into action. Changes to the data in other columns
do not. When this trigger detects an update that violates the trigger conditions, it cancels the
update and prints a message. If you would like to test this one, substitute the current day
of the week for "Saturday" or "Sunday".
create trigger stopupdatetrig
on titles
for update
as
/* If an attempt is made to change titles.title_id
** on Saturday or Sunday, cancel the update. */
if update (title_id)
and datename(dw, getdate())
in ("Saturday", "Sunday")
begin
rollback transaction
print "We do not allow changes to "
print "primary keys on the weekend."
end
Restricted update triggers with multiple actions
You can specify multiple trigger actions on more than one column using if update. The following
example modifies stopupdatetrig to include additional trigger actions for updates to titles.price or titles.advance. In addition to preventing updates to the primary key on weekends, it prevents
updates to the price or advance of a title, unless the total revenue amount for that title surpasses its advance amount. You can use the same trigger name because the modified trigger replaces
the old trigger when you create it again.
create trigger stopupdatetrig
on titles
for update
as
if update (title_id)
and datename(dw, getdate())
in ("Saturday", "Sunday")
begin
rollback transaction
print "We do not allow changes to"
print "primary keys on the weekend!"
end
if update (price) or update (advance)
if exists (select * from inserted
where (inserted.price * inserted.total_sales)
< inserted.advance)
begin
rollback transaction
print "We do not allow changes to price or"
print "advance for a title until its total"
print "revenue exceeds its latest advance."
end
The next example, created on titles, prevents update if any of the following conditions is true:
The user tries to change a value in the primary key title_id in titles
The dependent key pub_id is not found in publishers
The target column does not exist or is null
Before you run this example make sure the following error messages exist in sysusermessages:
sp_addmessage 35004, "titles_utrg - Update Failed: update of primary keys %1! is not allowed."
sp_addmessage 35005, "titles_utrg - Update Failed: %1! not found in authors."
The trigger is as follows:
create trigger title_utrg
on titles
for update as
begin
declare @num_updated int,
@col1_var varchar(20),
@col2_var varchar(20)
/* Determine how many rows were updated. */
select @num_updated = @@rowcount
if @num_updated = 0
return
/* Ensure that title_id in titles is not changed. */
if update(title_id)
begin
rollback transaction
select @col1_var = title_id from inserted
raiserror 35004 , @col1_var
return
end
/* Make sure dependencies to the publishers table are accounted for. */
if update(pub_id)
begin
if (select count(*) from inserted, publishers
where inserted.pub_id = publishers.pub_id
and inserted.pub_id is not null) != @num_updated
begin
rollback transaction
select @col1_var = pub_id from inserted
raiserror 35005, @col1_var
return
end
end
/* If the column is null, raise error 24004 and rollback the
** trigger. If the column is not null, update the roysched table
** restricting the update. */
if update(price)
begin
if exists (select count(*) from inserted
where price = null)
begin
rollback trigger with
raiserror 24004 "Update failed : Price cannot be null. "
end
else
begin
update roysched
set lorange = 0,
hirange = price * 1000
from inserted
where roysched.title_id = inserted.title_id
end
end
end
To test for the first error message, 35004, enter:
update titles
set title_id = "BU7777"
where title_id = "BU2075"
To test for the second error message, 35005:
update titles
set pub_id = "7777"
where pub_id = "0877"
To test for the third error, which generates message 24004:
update titles
set price = 10.00
where title_id = "PC8888"
This query fails because the price column in titles is null. If it were not null, it would have updated
the price for title PC8888 and performed the necessary recalculations for the roysched table.
Error 24004 is not in sysusermessages but it is valid in this case. It demonstrates the
"rollback trigger with raiserror" section of the code.
Updating a foreign key
A change or an update to a foreign key by itself is probably an error. A foreign key is just a
copy of the primary key. Never design the two to be independent. If you want to allow updates
of a foreign key, you should protect integrity by creating a trigger that checks updates
against the master table and rolls them back if they do not match the primary key.
In the following example, the trigger tests for two possible sources of failure: either the title_id
is not in the salesdetail table or it is not in the titles table.
This example uses nested if...else statements. The first if statement is true when the value
in the where clause of the update statement does not match a value in salesdetail, that is,
the inserted table will not contain any rows, and the select returns a null value. If this test is
passed, the next if statement ascertains whether the new row or rows in the inserted table
join with any title_id in the titles table. If any row does not join, the transaction is rolled back,
and an error message is printed. If the join succeeds, a different message is printed.
create trigger forupdatetrig
on salesdetail
for update
as
declare @row int
/* Save value of rowcount. */
select @row = @@rowcount
if update (title_id)
begin
if (select distinct inserted.title_id
from inserted) is null
begin
rollback transaction
print "No, the old title_id must be in"
print "salesdetail."
end
else
if (select count(*)
from titles, inserted
where titles.title_id =
inserted.title_id) != @row
begin
rollback transaction
print "No, the new title_id is not in"
print "titles."
end
else
print "salesdetail table updated"
end
***************************************************************************************
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/ alintilar bu adresten .
cok fazla incelemeye vaktim olmadi siteyi yani . ama belki isine yarar bi bak istersen.
kolay gelsin...
< Bu mesaj bu kişi tarafından değiştirildi tamers -- 22 Ocak 2006 17:15:22 >
Sayfa:
1
Ip işlemleri
Bu mesaj IP'si ile atılan mesajları ara Bu kullanıcının son IP'si ile atılan mesajları ara Bu mesaj IP'si ile kullanıcı ara Bu kullanıcının son IP'si ile kullanıcı ara
KAPAT X
Bu mesaj IP'si ile atılan mesajları ara Bu kullanıcının son IP'si ile atılan mesajları ara Bu mesaj IP'si ile kullanıcı ara Bu kullanıcının son IP'si ile kullanıcı ara
KAPAT X
