Trigger

• pallavi.unde

  SSCommitted

  Points: 1987

  More actions

  December 31, 2011 at 4:33 am

  #248920

  I followed below steps for trigger..

  Create Table Stu_Table( Stu_Id int, Stu_Name varchar(15),Stu_Class int)

  create table stu_log( user_id VARCHAR(15), description VARCHAR(100))

  insert into stu_table values(1, 'Komal',10)

  insert into stu_table values(2, 'Ajay',10)

  insert into stu_table values(3, 'Santosh',10)

  insert into stu_table values(4, 'Rakesh',10)

  insert into stu_table values(5, 'Bhau',10)

  select * from stu_table

  CREATE TRIGGER stu_delete

  before delete ON stu_table FOR EACH ROW

  BEGIN

  INSERT into stu_log(user_id, description)

  VALUES (user(), CONCAT('Record deleted ',old.stu_id,'

  ',old.stu_name,' ',old.stu_class));

  insert into stu_table_backup values(old.stu_id,old.stu_name,old.stu_class)

  but scripts showing syntax error....

  Thanks & Regards,
  Pallavi

• Devendrakumar

  SSC-Forever

  Points: 42493

  More actions

  December 31, 2011 at 4:49 am

  #1427200

  SQL Server doesn't have before triggers...

  CREATE TRIGGER [ schema_name . ]trigger_name

  ON { table | view }

  [ WITH <dml_trigger_option> [ ,...n ] ]

  { FOR | AFTER | INSTEAD OF }

  { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

  [ WITH APPEND ]

  [ NOT FOR REPLICATION ]

  AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

  For More: http://msdn.microsoft.com/en-us/library/ms189799.aspx

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  December 31, 2011 at 4:53 am

  #1427201

  pallavi.unde (12/31/2011)

  ---

  CREATE TRIGGER stu_delete

  before delete ON stu_table FOR EACH ROW

  BEGIN

  INSERT into stu_log(user_id, description)

  VALUES (user(), CONCAT('Record deleted ',old.stu_id,'

  ',old.stu_name,' ',old.stu_class));

  insert into stu_table_backup values(old.stu_id,old.stu_name,old.stu_class)

  That looks kinda like Oracle or MySQL syntax. SQL Server has neither Before triggers nor does it have FOR EACH ROW, nor does it have a CONCAT function. (Oracle and MySQL have all three)

  Are you using SQL Server and trying to convert Oracle or MySQL code, or are you writing a trigger for an Oracle or MySQL database?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 31, 2011 at 6:10 am

  #1427207

  I see the same thing Gail does...that's looking like an ORACLE trigger;

  SQL triggers handle all the rows at the same time,and not for each row via the specially materialized INSERTD and DELETED tables inside teh trigger;

  i think this is what you are after...

  since the insert has already occurred, we just need to do the auditing of the DELETED table.

  CREATE TRIGGER stu_delete

  ON stu_table

  FOR DELETE

  AS

  BEGIN

  --the log

  INSERT INTO stu_log

  (user_id,

  description)

  SELECT USER_NAME(),

  'Record deleted '

  + CONVERT(VARCHAR, DELETED.stu_id)

  + ','

  + DELETED.stu_name

  + ','

  + old.stu_class

  FROM DELETED

  --the backup

  INSERT INTO stu_table_backup

  SELECT stu_id,

  stu_name,

  stu_class

  FROM DELETED

  END --TRIGGER

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!