Results 1 to 6 of 6

Thread: easy trigger

  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Unanswered: easy trigger

    I create trigger no table person. When I invoke sql command
    insert into person values (.... bla bala bal)
    trigger is autommaticaly fired.
    The trigger must log sql commads, so i must insert into table log this command, whos execute triger - insert into person values (.... bla bala bal).
    How I can do it?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Make sure your database is set to allow cascading triggers, then your trigger should be able to both log its own insert and initiate other inserts.

    blindman

  3. #3
    Join Date
    Jan 2004
    Posts
    7
    This in no problem.
    Problem is:
    How to find out what statement is making the trigger run and get this statement in trigger code

    Originally posted by blindman
    Make sure your database is set to allow cascading triggers, then your trigger should be able to both log its own insert and initiate other inserts.

    blindman

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The trigger has no idea what caused the insert, update, or delete. The best you can do is reference some of the nyladic functions (look them up) that return login and user information. Then you can at least see WHO or WHAT LOGIN initiated the process.

    blindman

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well...you can tell what type...

    INSERT: Rows in inserted, none in deleted
    DELETE: Rows in deleted, mone in inserted
    UPDATE: Rows in both

    ...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The following example uses a table called PARENT as the base for INSERT TRIGGER:

    Code:
    if object_id('dbo.tblLog') is not null
       drop table dbo.tblLog
    go
    create table dbo.tblLog (
       EventType  varchar(15)   null,
       Parameters int           null,
       EventInfo  varchar(8000) null)
    go
    if object_id('dbo.trig_parent') is not null
       drop trigger dbo.trig_parent
    go
    create trigger dbo.trig_parent on dbo.parent for insert as
       set nocount on
       declare @cmd varchar(8000)
       set @cmd = 'create table #tbl (
          EventType  varchar(15)   null,
          Parameters int           null,
          EventInfo  varchar(8000) null);'
       set @cmd = @cmd + 'insert #tbl '
       set @cmd = @cmd + 'exec ('
       set @cmd = @cmd + char(39)+'dbcc inputbuffer (' + cast(@@spid as varchar(25)) + ') '
       set @cmd = @cmd + 'with no_infomsgs' + char(39)
       set @cmd = @cmd + ');'
       set @cmd = @cmd + 'insert dbo.tblLog select * from #tbl'
       exec (@cmd)
    go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •