SQL Server Performance

error with SELECT INTO in trigger

Discussion in 'SQL Server 2005 General DBA Questions' started by espirito, Nov 20, 2007.

  1. espirito New Member

    I am sorry everybody - I have solved this problem.
    Well - I still don't understand why it is so, but now it works.
    The thing wasn't in the select * into command - but it was because I had more triggers for update of this table.
    I have disabled them and now it is working.
    Sorry for confusing you - but really thanks! I do appreciate your trying to help me! [Y]
    I wish all of you the best [:)]
    Milan
  2. aynur New Member

    Hello,
    I have a question related to a trigger function.My problem is,
    I have a sql query which stores user logs and user's requested queries.I have tried this, but it is not storing any logs to a table.My code is;
    CREATE TRIGGER trgLog
    ON master..log_tbl FOR insert
    AS
    BEGIN
    Select sp.spid , loginame=rtrim(sp.loginame), sp.hostname, dbname = (case when sp.dbid = 0 then null when sp.dbid <> 0 then db_name(sp.dbid) end),
    substring(sql.text, stmt_start/2,CASE WHEN stmt_end<1 THEN 8000 ELSE (stmt_end-stmt_start)/2 END) AS runningSqlText
    From master.dbo.sysprocesses sp (NOLOCK)
    cross apply sys.dm_exec_sql_text(sp.sql_handle) AS sql inner join sys.dm_exec_connections as sp_con on sp.spid = sp_con.session_id
    END

    I am trying to create a trigger for this query.My trigger will use SELECT AND INSERT functions to store those logs into a table.
    Thank you.
  3. Adriaan New Member

    All your trigger does is execute a SELECT query whenever data is inserted into master..log_tbl.
    You sound as if you want to execute an INSERT query?
  4. aynur New Member

    I want to insert select query's values into my log_tbl. But i couldn't do it. Can you give me a sample code please!!
    Thanks.
  5. Adriaan New Member

    Look up the INSERT keyword in Books Online.
    I'm a bit surprised that you would need to ask about this, given the complexity of the SELECT statement that you posted - unless of course you're going in at the deep end, without proper training in SQL.
    ... anyway ---- a trigger is a piece of code that executes after another process has inserted/updated/deleted data on that table. This is not what you're looking for.
  6. aynur New Member

    Firstoffall, I have already checked the book online and the samples but I could not still make it work.My aim is to, store the user's requested queries to my log_table.I am getting these logs from a the view's of my master database.But the thng is, those valus are stored in the view temporarly.So I need to write a trigger for it to get those values( running sql codes) from the views and to store into a specific table which I already created as (log_tbl).
    So if possible, without suggesting me to check the samples, Can u help me to write the trigger and make it work?
    I appriciate ur interests and responds.
    If you execute my select query, you will see what you did in database server. I want to insert these values into table. I think i can do it with trigger. But i
    couldn't do.
    Please execute this code in yoru database;Select
    sp.spid , substring(sql.text, stmt_start/2,CASE WHEN stmt_end<1 THEN 8000 ELSE (stmt_end-stmt_start)/2 END)AS runningSqlText From master.dbo.sysprocesses sp (NOLOCK)
    cross
    apply sys.dm_exec_sql_text(sp.sql_handle) AS sql inner join sys.dm_exec_connections as sp_con on sp.spid = sp_con.session_id
    Thanks.
  7. Adriaan New Member

    You're putting the cart in front of the horse ...
    An insert trigger executes after data has been inserted into the table (or view).
    ************
    By the way, the title you gave to the thread makes it clear that you're missing the point as to triggers and queries: there is no such thing as an "INSERT INTO trigger".
    There is the INSERT INTO clause which you can put in front of a SELECT query or a VALUES list, and there is an "insert trigger".

Share This Page