Posted on 2008-08-12 15:52
Prayer 閱讀(228)
評論(0) 編輯 收藏 引用 所屬分類:
數據庫,SQL
*****************************************************************************************
視圖,存儲過程和觸發器
*****************************************************************************************
創建視圖:(查詢數據,用于系統統計報表)
create view dept_sum_vu
(name,minsal,maxsal,avgsal)
as select d.department_name,min(e.salary),max(e.salary),avg(e.salary)
from employees e,departments d
where e.department_id = d.department_id
group by d.department_name;
存貯過程,觸發器和刪除外鍵
1).存貯過程:
存貯過程的路徑:打開Stored Procedures中new一個新的Stored Procedures,中間填寫代碼,代碼如下:
CREATE PROCEDURE sa.bbsTest AS
select Bbsid ,content , area_id , name FROM BBs a JOIN bbs_area b
on a.area_id = b.id
CREATE PROCEDURE bbsTest AS
select Bbsid ,content , area_id , name FROM BBs a JOIN bbs_area b
on a.area_id = b.id
2).觸發器
打開用戶表項,點設計表,選中其中一個字段,右鍵task點manager triggers,在彈出的表單中寫代碼:
CREATE TRIGGER [tD_bbs_area] ON [dbo].[bbs_area]
FOR DELETE
AS
BEGIN
DELETE rebbs
FROM bbs, deleted
WHERE rebbs.bbs_id = bbs.bbsid
AND bbs.area_id = deleted.id
DELETE bbs
FROM deleted
WHERE bbs.area_id = deleted.id
END
3).刪除外鍵
打開用戶表項,點設計表,選中其中一個字段,右鍵relationships,在彈出的主鍵和外鍵中選擇就可以了.
刪除外鍵,需要先刪除主鍵表里面的有關外鍵的字段,再來刪除外鍵的有關字段.