1> /* Simple trigger examples */
2> /* */
3> /* Note: Triggers are based on tables in my database. */
4> /* For each trigger: */
5> /* Table structure and content shown first */
6> /* Trigger definition next (sp_helptext) */
7> /* Example showing result of trigger action */
8> /* */
9> /* To try out triggers like these, use similar tables in your*/
10> /* database, or create them first if necessary. */
11> /* */
12> use evelyn
1> /* */
2> /* Trigger showless: shows inserted row in table supplies */
3> /* */
4> select * from supplies
id product number unitprice
--- ---------- ------ ------------------------
6 stamps 100 0.32
3 erasers 50 1.24
4 notebooks 50 3.38
5 envelopes 50 0.23
7 rolodex 6 7.88
8 rulers 10 3.38
9 mice 33 2.50
10 notepads 50 1.50
(8 rows affected)
1> /* */
2> sp_helptext showless
# Lines of Text
---------------
1
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/* Make and test a better trigger - show fewer columns. */
create trigger showless
on supplies
for insert
as
select
supplies.id, supplies.product, supplies.number, supplies.unitprice
from supplies, inserted
where supplies.id = inserted.id
(1 row affected)
(return status = 0)
1> /* */
2> insert into supplies
3> values (1, 'pencils', 50, .98)
id product number unitprice
--- ---------- ------ ------------------------
1 pencils 50 0.98
(1 row affected)
(1 row affected)
1> /* */
2> /* Trigger countrows: updates averages when new data inserted */
3> /* */
4> select * from decimals
degrees mm wind
-------- ------- ----
90.00 760.2 7
33.00 759.7 2
45.50 763.0 3
30.00 755.7 10
10.00 750.0 5
75.00 762.5 8
(6 rows affected)
1> /* */
2> sp_helptext countrows
# Lines of Text
---------------
1
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create trigger countrows
on decimals
for insert
as
select "Average atm. pressure and wind speed now", avg(mm), avg(wind),
"for the", count(*), "entries in this table."
from decimals
(1 row affected)
(return status = 0)
1> /* */
2> insert into decimals
3> values (75, 762.5, 8)
----------------------------------------
---------------------------------------------------- -----------
------- ----------- ----------------------
Average atm. pressure and wind speed now
759.085714285714 6
for the 7 entries in this table.
(1 row affected)
(1 row affected)