函數﹐過程﹐觸發器

来源：互联网发布：linux jdk版本查看编辑：程序博客网时间：2024/05/10 10:37

CREATE FUNCTION Func1
(@nTable char(20))
returns int
as
begin
return (select count(*) from @nTable))
end

CREATE FUNCTION Func1
(@nTable char(20))
returns int
as
begin
   DECLARE @nRow int
   set @nRow= (select count(*) from @nTable))
    return @nRow
end

CREATE FUNCTION Func1&&&&建立一程序﹐返回一個表中的記錄總數
(@nTable char(20))
returns int
as
begin
    declare @sql nvarchar(200)
    declare @count int
    set @sql=N'select @count=count(*) from '+@nTable &&Unicode 常量使用 N 開頭來指定：N'A Unicode string'。
    exec sp_executesql @sql,N'@count int output',@count output
    return @count
end

SELECT dbo.func1('t_jcs')

CREATE PROC Proc1&&建立一過程﹐返回一個表中的記錄總數
@nTable char(20)
as
exec('select count(*) from '+ @nTable)

EXEC proc1 't_jcs'

USE pubs&&打開數據庫
IF EXISTS (SELECT name FROM sysobjects   &&看看是否已經存在要建的觸發器﹐如果存在﹐則先刪除
      WHERE name = 'trig1' AND type = 'TR')
   DROP TRIGGER trig1
GO
CREATE TRIGGER trig1&&創建一觸發器﹐當刪除T_JCS表中的記錄時﹐把刪除的記錄顯示出來
ON t_jcs
for delete
as
select * from deleted/updated/ inserted
go