Re: SQL Trigger

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Niall (Niall_at_discussions.microsoft.com)
Date: 08/09/04 

Date: Mon, 9 Aug 2004 09:45:05 -0700

Thanks for the replies.

Unfortunatly the application logis in via standard sysdba login so I cant
tell who is who from using the username() funcions etc. I could check the
host_name() but the PC names do not equate to the usernames and I am not
going to even try to keep some kind of mapping table for this.

I dont think I can do what I want to do via triggers as I need to know their
accessID but cannot obvously pass that into the trigger as a paramater.

Its not too big a deal, its just a pity I couldn't put this logic into the
application, its a SalesLogix system and this particular part is not
customisable in it.

Thanks for your replies,
Niall

"Andrew John" wrote:

>
>
> Niall wrote:
> > Hi,
> > Which is the best trigger type to use in this situation...
> >
> > I need to check that a user has authority to edit any certain row in a
> > table. If their accessid is not held against the row they are trying to edit
> > then I must prevent the update. Otherwise if their accessid is held against
> > the row then they update can conintue.
> >
> > Sounds like an "Instead of Update"? How can I let the update continue
> > without having to write sql in the trigger to finish it off so I dont have to
> > go using the "Inserted" table etc.
> >
> > Thanks
> > Niall
>
> An after trigger will work too - just use "rollback" if you want to
> reject the update. Of course a trigger can't be passed values, so it
> may be a bit difficult to reference the users "accessid". You don't say
> how the trigger tests your condition.
>
> The very basics are:
>
> create trigger CheckPermissions on MyTable
> after update
> as
>
> if exists ( select * from inserted where SomeCol = SomeOtherCol)
> rollback tran
>
> return
>
>
>
> Regards
> AJ
>

Relevant Pages

  • Re: suser_sname trigger (audit records)
    ... >insert into tblBank ... This trigger will fail. ... with values for the userstamp and username columns only. ... I don't see anything wrong with this trigger. ...
    (microsoft.public.sqlserver.programming)
  • Setting of @@FETCH_STATUS
    ... I have a snippet of code from a trigger: ... WHERE Name = @UserName) ... MSDN page on @@FETCH_STATUS seems to imply that my select won't set it. ...
    (microsoft.public.sqlserver.programming)
  • login form to login table
    ... hi, i have a login table with user name and password, and a form that require ... a username and password to trigger another form, i have connected my form to ... vba in access ...
    (microsoft.public.access.forms)
  • login table and login form
    ... i have a login table with user name and password, ... a username and password to trigger another form, i have connected my form to ... vba in access ...
    (microsoft.public.access.formscoding)
  • Trigger question
    ... first one is created by following script ... edit or delete I need to have the updated contents ... addnews, edits and deletes I have to log in this way and I would like to ... avoid having to write the fields and values list in detail in each trigger. ...
    (microsoft.public.sqlserver.programming)