Database Reference
In-Depth Information
Listing 8-14. Logon trigger
create trigger trg_Logon_BusinessHoursOnly
on all server
for logon
as
begin
declare
@currTime datetime = current_timestamp
if original_login() = 'HRLogin' and
( -- Check if today is weekend
((@@datefirst + datepart(dw, @currTime)) % 7 in (0,1)) or
(cast(@currTime as time) >= '18:00:00') or
(cast(@currTime as time) < '8:00:00')
)
rollback
end
Like DDL triggers, there is an EVENTDATA function that returns XML with additional information about a logon
event. An example of this XML code follows.
<EVENT_INSTANCE>
<EventType>LOGON</EventType>
<PostTime>2013-05-12T17:55:40.090</PostTime>
<SPID>55</SPID>
<ServerName>SQL2012-STD1\SQL2012</ServerName>
<LoginName>SQL2012-STD1\Administrator</LoginName>
<LoginType>Windows (NT) Login</LoginType>
<SID>sid</SID>
<ClientHost>&lt;local machine&gt;</ClientHost>
<IsPooled>0</IsPooled>
</EVENT_INSTANCE>
You need to make sure that the logon trigger executes as fast as possible to prevent possible connection timeouts.
You need to be very careful if the trigger is accessing external resources where response time is not guaranteed. Think
about a CLR function that performs additional authentication against a corporate Active Directory as an example.
That function needs to set a short timeout for AD queries and correctly handle the possible exceptions. Otherwise,
nobody would be able to login to SQL Server.
UPDATE( ) and COLUMNS_UPDATED( ) functions
The UPDATE and COLUMNS_UPDATED functions allow you to check if specific columns were affected by insert or
update operations.
The UPDATE function accepts a column name as the parameter and returns a Boolean value that shows if the
column was affected by the statement that fires the trigger. For insert operations, it always returns TRUE. For update
operations, it would return TRUE if an attempt was made or, more specifically, if a column was present in the list of
columns that needed to be updated, regardless of whether it changed the value or not. For example, in the Listing 8-15,
update statement does not change the value of column C in the row. Although, update(C) function in the trigger
return TRUE because column C was included in the list of the columns in update statement.
 
Search WWH ::




Custom Search