sql语句大全之15. DML, DDL, LOGON 触发器(2)

当前位置:

首页 > 数据库 > T-SQL >

sql语句大全之15. DML, DDL, LOGON 触发器(2)

IDENTITY(1,1) primary key, LOGIN_NAME nvarchar(1024), LOGIN_TIME datetime ) GO IF EXISTS(select 1 from sys.server_triggers where name = 'login_history_trigger') DROP TRIGGER login_history_trigger ON ALL SERVER GO CREATE TRIGGER login_history_trigger ON ALL SERVER FOR LOGON AS BEGIN --IF SUSER_NAME() NOT LIKE 'NT AUTHORITY\%' AND -- SUSER_NAME() NOT LIKE 'NT SERVICE\%' IF ORIGINAL_LOGIN() NOT LIKE 'NT AUTHORITY\%' AND ORIGINAL_LOGIN() NOT LIKE 'NT SERVICE\%' BEGIN INSERT INTO DBA..login_history VALUES(ORIGINAL_LOGIN(),GETDATE()); END; END; GO --view login history after logon SELECT * FROM login_history

代码示例2： 限制特定用户在特定时间范围登录、限制连接数

--限制下班时间不能登录
DROP TRIGGER IF EXISTS limit_user_login_time ON ALL SERVER
GO
CREATE TRIGGER limit_user_login_time
ON ALL SERVER FOR LOGON 
AS
BEGIN
    IF ORIGINAL_LOGIN() = 'TestUser' 
       AND (DATEPART(HOUR, GETDATE()) < 9 OR DATEPART (HOUR, GETDATE()) > 18)
    BEGIN
        PRINT 'TestUser can only login during working hours!'
        ROLLBACK
    END
END
GO

--限制连接数
DROP TRIGGER IF EXISTS limit_user_connections ON ALL SERVER
GO
CREATE TRIGGER limit_user_connections
ON ALL SERVER 
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    IF ORIGINAL_LOGIN() = 'TestUser' 
       AND (SELECT COUNT(*) FROM   sys.dm_exec_sessions
            WHERE  Is_User_Process = 1 
            AND Original_Login_Name = 'TestUser') > 2
    BEGIN
        PRINT 'TestUser can only have 1 active session!'
        ROLLBACK
    END
END

注意：如果LOGON触发器把所有人都锁在外面了怎么办？

Logon failed for login 'TestUser' due to trigger execution.

这时，只能通过DAC登录SQL Server去禁用LOGON触发器/修改逻辑以允许登录，DAC登录方式有远程和本地两种，远程登录需要通过sp_configure 开启remote admin connections ，如果没有事先开启，那就只能选择本地登录方式：

服务器本地，在SSMS中通过DAC登录

服务器本地，在cmd中通过DAC登录

--禁用/启用LOGON触发器
DISABLE TRIGGER limit_user_connections ON ALL SERVER
ENABLE TRIGGER limit_user_connections ON ALL SERVER

参考：

CREATE TRIGGER (Transact-SQL)

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017

Create Nested Triggers

https://docs.microsoft.com/en-us/sql/relational-databases/triggers/create-nested-triggers?view=sql-server-2017

Transact-SQL statements

https://docs.microsoft.com/en-us/sql/t-sql/statements/statements?view=sql-server-2017

Why we can‘t use commit in trigger, can anyone give proper explanation

https://community.oracle.com/thread/1082134

Database PL/SQL Language Reference, Using Triggers

https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS020

栏目列表