i am trying to execute the before insert trigger , but i m getting the sql errors

what i want to achieve is i have a table called orders. i want to perform the before insert trigger on my开发者_StackOverflow中文版 orders table.i want to capture the username of person performing INSERT into table. one table called info which contain the user.

this is my code

create table orders
(
  order_id int,
  quantity  int,
  cost int,
  total_cost int,
  created_date datetime,
  created_by varchar(20)
)

create  trigger beforeInsertdata
 before insert
  on orders
 for each row
  declare 
 v_username varchar2(10);
begin
 -- Find username of person performing INSERT into table

    SELECT user INTO v_username
    FROM info;
 -- Update create_date field to current system date
    :new.create_date := sysdate;
 -- Update created_by field to the username of the person performing the INSERT
    :new.created_by := v_username;

END;

--user information--
create table info
(
  userid int ,
  user_name varchar(10)
)

insert into info values(1,'vivek')
select * from info

---

Basically, triggers are classified into two main types:-

1)After Triggers (For Triggers)

2)Instead Of Triggers

and the syntax for trigger is

  CREATE TRIGGER trigger_name ON table_name 
  [FOR|AFTER|INSTEAD OF] [INSERT|UPDATE|DELETE]

  AS

  //your code goes here   

  GO

NOTE : FOR keyword used for INSERT |UPDATE Command where as AFTER USED FOR DELETE Command.

---

It's hard to tell what you're really trying to do. I've modified your code sample so that it will work on SQL2K5 and made some assumptions about how you're wanting to use the connected user account.

CREATE TABLE orders (
  order_id int,
  quantity int,
  cost int,
  total_cost int,
  created_date datetime,
  created_by varchar(20)
);

CREATE TABLE info (
  userid int,
  user_name varchar(10)
);

INSERT INTO info
VALUES (1, 'vivek');

SELECT *
FROM info;

CREATE TRIGGER orders_InsteadOfInsert ON orders
INSTEAD OF INSERT AS BEGIN
  SET NOCOUNT ON;

  -- varchar(10) is to match your table, but probably should be larger
  DECLARE @CurrentUser VarChar(10);
  SELECT @CurrentUser = SYSTEM_USER;

  IF (@CurrentUser NOT IN (SELECT user_name FROM info)) BEGIN
    -- consider using an identity column for the key instead of this
    INSERT INTO info (userid, user_name)
    SELECT
      ISNULL((SELECT MAX(userid) FROM info), 0) + 1,
      @CurrentUser;
  END;

  INSERT INTO orders (order_id, quantity, cost, total_cost, created_date, created_by)
  SELECT
    INS.order_id,
    INS.quantity,
    INS.cost,
    INS.total_cost,
    GETDATE(),
    @CurrentUser
  FROM INSERTED INS;
END;