Creating login triggers

Create a login trigger as a stored procedure. Do not use the create trigger command. The following sample requires the you first create the lookup table in the pubs2 database:

create table lookup (
  appname varchar(20),
  attr varchar(20),
  value varchar(20),
  login varchar(20)
)

Then create a login trigger stored procedure in the pubs2 database:

create procedure loginproc as
  declare @appnamevarchar(20)
  declare @attr varchar(20)
  declare @value varchar(20)
  declare @retvalue int
declare apctx cursor for
select appname, attr, value from
pubs2.dbo.lookup where login = suser_name()
open apctx
fetch apctx into @appname, @attr, @value

While (@@sqlstatus = 0)
  begin
    select f@retval = 
      set_appcontext (rtrim (@appname),
      rtrim(@attr), rtrim(@value))
  fetch apctx into @appname, @attr, @value
 end
go

Grant permission to execute loginproc to public:

grant execute on loginproc to public

To associate the login trigger with a specific user, run alter login in the user’s default database.