Trigger Code

• sridhar.yalamanchi

  SSChasing Mays

  Points: 628

  More actions

  September 5, 2009 at 6:29 am

  #156180

  Please provide me sample codes for using trigger. And please provide me trigger interview questions.

  Thanks in advance.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  September 5, 2009 at 6:35 am

  #1049732

  well, here's an example of a trigger...in this case, my trigger is keeping track of the last time a column was updated.

  for trigger interview questions, I'm afraid I can't help you. If you are not familiar with the syntax and usage,,coaching questions will not get you over that; only experience will.

  Practice with this trigger, use the search to find other forum posts on triggers, and most importantly, copy/paste/test the examples!

  that's the only way to garner experience.

  CREATE TABLE WHATEVER(

  WHATEVERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

  DESCRIP VARCHAR(30)

  )

  INSERT INTO WHATEVER(DESCRIP)

  SELECT 'APPLES' UNION

  SELECT 'ORANGES' UNION

  SELECT 'BANANAS' UNION

  SELECT 'GRAPES' UNION

  SELECT 'CHERRIES' UNION

  SELECT 'KIWI'

  ALTER TABLE WHATEVER ADD INSERTDT DATETIME DEFAULT GETDATE()WITH VALUES,

  UPDATEDDT DATETIME DEFAULT GETDATE() WITH VALUES

  SELECT * FROM WHATEVER

  CREATE TRIGGER TR_WHATEVER

  ON WHATEVER

  FOR INSERT,UPDATE

  AS

  UPDATE WHATEVER

  SET UPDATEDDT = GETDATE()

  FROM INSERTED

  WHERE WHATEVER.WHATEVERID=INSERTED.WHATEVERID

  INSERT INTO WHATEVER(DESCRIP)

  SELECT 'CANTALOUPE' UNION

  SELECT 'TANGARINES' UNION

  SELECT 'PLUMS' UNION

  SELECT 'PEACHES' UNION

  SELECT 'NECTARINES'

  SELECT * FROM WHATEVER

  UPDATE WHATEVER SET DESCRIP = DESCRIP + ' ' WHERE WHATEVERID IN (4,5)

  SELECT * FROM WHATEVER

  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!

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736478

  More actions

  September 5, 2009 at 10:26 am

  #1049759

  You'll have to do some work. I might start here:

  http://msdn.microsoft.com/en-us/library/aa214450%28SQL.80%29.aspx

• Marcin Gol

  Hall of Fame

  Points: 3314

  More actions

  September 6, 2009 at 7:55 am

  #1049852

  Yes! all of us should do search in BOL before asking here/other places 😉 Books Online are really good, they have good examples (with code samples), and in many cases you can find ther in depth description).

  On my notebook I have BOL to sql 2000/2005/2008.