Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005
    Posts
    91

    Unanswered: Insert Trigger Using Variables Help

    Can somebody please help me with compiling my insert trigger below. I am fairly new to SQL server 2000 and I am having troubles with using variables in insert triggers. The trigger that I am creating will basically update another table based on a certain criteria that is not specified below. I am hoping to first get my trigger to work then apply the criteria on when to fire afterwards. I just need help with being able to store values in my declared variables for insert into another table. Thanks in advance for everyones help.




    Use database_testing

    IF EXISTS (SELECT name FROM sysobjects
    WHERE type = 'TR' AND name = 'Trigger_Name')
    DROP TRIGGER Trigger_Name
    GO

    CREATE TRIGGER Trigger_Name
    ON [trigger_table] FOR INSERT
    AS
    Declare @resource_id int = inserted.resource
    @type = varchar(100) = inserted.type
    @date_logged (datetime) = inserted.creation_date
    @created varchar(100) = inserted.username

    Insert into table_A (resource_id, resource_type, date_created, created_by)
    values (resource_id,type, date_logged, created)

  2. #2
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    You didn't specified that exactly what you want to fulfill. I think you want to track users for insert row or update row.

    Code:
    CREATE TRIGGER Trigger_Name
    ON [trigger_table] FOR INSERT
    AS
    
    SET NOCOUNT ON 
    
    Begin
    Insert into table_A (resource_id, resource_type, date_created, created_by)
    SELECT inserted.resource
               inserted.type,
               inserted.creation_date,
               inserted.username
    FROM inserted
    End
    
    SET NOCOUNT OFF
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  3. #3
    Join Date
    Dec 2005
    Location
    Tilburg, Netherlands
    Posts
    73
    rajeshpatel gave you probably the nicest sollution. If you want to hold on to your own script for some reason, I filtered some errors out of it. This is what it should look like:

    Code:
    CREATE TRIGGER Trigger_Name
    ON [trigger_table] FOR INSERT
    AS
    BEGIN
            Declare @resource_id int
            ,       @type varchar(100)
            ,       @date_logged datetime
            ,       @created varchar(100)
    
            select  @resource_id = inserted.resource
            ,       @type = inserted.type
            ,       @date_logged = inserted.creation_date
            ,       @created = inserted.username
    
            Insert  into table_A 
            (resource_id, resource_type, date_created, created_by)
            values 
            (@resource_id,@type, @date_logged, @created)
    ...
    END
    Gr,
    Yveau

    > SELECT * FROM users WHERE clue > 0;
    Empty set (0.00 sec)


Posting Permissions

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