-- -- set language n'简体中文'
-- -- declare @b int
-- --
-- -- set @b= datename(month,getdate())
-- -- select @b
-- -- go
-- -- declare @a int
-- --
-- -- set @a=datepart(year,getdate())
-- -- select @a
-- --
-- -- go
-- -- declare @c varchar(50)
-- -- set @c= datename(year,getdate())+n'year'+cast(datepart(month,getdate()) as varchar)+n'month'+datename(day,getdate())+n'day'
-- -- select @c
-- --
-- -- select convert(varchar(10),getdate(),120)
-- --
-- -- select convert(char(11),datename(year,getdate()))+'1-1'
-- --
-- -- select convert(char(12),getdate(),114)
--
-- -- create function selectname
-- -- (
-- -- @id varchar(11)
-- -- )
-- -- returns table
-- -- as
-- -- return ( select au_lname from authors where au_id=@id)
--
-- -- select * from selectname('213-46-8915')
-- create view tt
-- as
-- select * from selectname('213-46-8915')
-- create procedure dd
-- @a varchar(11)
-- as
-- select * from selectname(@a)
-- exec dd '213-46-8915'
-- USE pubs
-- IF EXISTS (SELECT name FROM sysobjects
-- WHERE name = 'reminder' AND type = 'TR')
-- DROP TRIGGER reminder
-- GO
-- CREATE TRIGGER reminder
-- ON titles
-- FOR INSERT, UPDATE, DELETE
-- AS
-- EXEC master..xp_sendmail 'MaryM',
-- 'Don''t forget to print a report for the distributors.'
-- GO
-- select 列名=name from syscolumns where id=object_id(N'authors')
-- create trigger trt on authors
-- for update
-- as
-- update forTrigger set dd='3333'
-- update authors set au_lname='fff' where au_id='213-46-8915'
USE pubs
GO
-- Turn recursive triggers ON in the database.
ALTER DATABASE pubs
SET RECURSIVE_TRIGGERS ON
GO
CREATE TABLE emp_mgr (
emp char(30) PRIMARY KEY,
mgr char(30) NULL FOREIGN KEY REFERENCES emp_mgr(emp),
NoOfReports int DEFAULT 0
)
GO
CREATE TRIGGER emp_mgrins ON emp_mgr
FOR INSERT
AS
DECLARE @e char(30), @m char(30)
DECLARE c1 CURSOR FOR
SELECT emp_mgr.emp
FROM emp_mgr, inserted
WHERE emp_mgr.emp = inserted.mgr
OPEN c1
FETCH NEXT FROM c1 INTO @e
WHILE @@fetch_status = 0
BEGIN
UPDATE emp_mgr
SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Add 1 for newly
WHERE emp_mgr.emp = @e -- added employee.
FETCH NEXT FROM c1 INTO @e
END
CLOSE c1
DEALLOCATE c1
GO
-- This recursive UPDATE trigger works assuming:
-- 1. Only singleton updates on emp_mgr.
-- 2. No inserts in the middle of the org tree.
CREATE TRIGGER emp_mgrupd ON emp_mgr FOR UPDATE
AS
IF UPDATE (mgr)
BEGIN
UPDATE emp_mgr
SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Increment mgr's
FROM inserted -- (no. of reports) by
WHERE emp_mgr.emp = inserted.mgr -- 1 for the new report.
UPDATE emp_mgr
SET emp_mgr.NoOfReports = emp_mgr.NoOfReports - 1 -- Decrement mgr's
FROM deleted -- (no. of reports) by 1
WHERE emp_mgr.emp = deleted.mgr -- for the new report.
END
GO
-- Insert some test data rows.
INSERT emp_mgr(emp, mgr) VALUES ('Harry', NULL)
INSERT emp_mgr(emp, mgr) VALUES ('Alice', 'Harry')
INSERT emp_mgr(emp, mgr) VALUES ('Paul', 'Alice')
INSERT emp_mgr(emp, mgr) VALUES ('Joe', 'Alice')
INSERT emp_mgr(emp, mgr) VALUES ('Dave', 'Joe')
GO
SELECT * FROM emp_mgr
GO
-- Change Dave's manager from Joe to Harry
UPDATE emp_mgr SET mgr = 'Harry'
WHERE emp = 'Dave'
GO
SELECT * FROM emp_mgr
GO
