Database interview questions - triggers

The post entitled Database interview questions and answers covered some trigger based questions like

• When to not use a trigger?
• Where to use a trigger?

Triggers give you control just before data is changed and just after the data is changed. This allows for:

• Auditing. 
• Validation and business security checking if so is desired. Because of this type of control, you can do tasks such as column formatting before and after inserts into database.

In this post, I will give an example of a trigger SQL code for Sybase database.

The following trigger compares the ReportForecastId values from the inserted table with those from the ReportForecast table. When you insert a new foreign key row, make sure the foreign key matches a primary key. The trigger should check for joins between the inserted rows (using the inserted table) and the rows in the primary key table, and then roll back any inserts of foreign keys that do not match a key in the primary key table.

use my_schema
go

setuser 'dbo'
go

IF OBJECT_ID('dbo.ReportForecast_I') IS NOT NULL
BEGIN
    DROP TRIGGER dbo.ReportForecast_I
    IF OBJECT_ID('dbo.ReportForecast_I') IS NOT NULL
        PRINT '<<< FAILED DROPPING TRIGGER dbo.ReportForecast_I >>>'
    ELSE
        PRINT '<<< DROPPED TRIGGER dbo.ReportForecast_I >>>'
END
go

CREATE TRIGGER ReportForecast_I ON ReportForecast FOR INSERT AS

BEGIN
 DECLARE  @numrows int,
  @nullcnt int,
  @validcnt int,
  @insReportForecastId numeric(9,0),
  @errno   int,
  @errmsg  varchar(255),
  @ValidateCode CdStd

 SELECT @numrows = @@rowcount
 SELECT @errno = @@error      

 IF @numrows = 0
  RETURN
 /* Sybase bug allows triggers to be called after error - we rollback */
 IF @errno!=0
 BEGIN
  SELECT @errno = 997
  SELECT @errmsg = " should not have fired. Rollback"
  GOTO error
 END /* of Header */

 /***** Start of MAIN BODY *****/ 

 /* All Code Table Validations Go Here*/ 

 /* All Parent-Child Relationship Checks Go Here */ 
 
 
 


 /* Now, Depending on what operation we are going to perform we will differentiate between them like
 ** 'I' for inssert, 'U'  for update, and 'D' for delete . The audits are essentially the same but there
    */
 IF OBJECT_ID('mydb..ReportForecast_Audit') IS NOT NULL
 BEGIN 
  INSERT INTO mydb..ReportForecast_Audit (
   Audit_ModifiedDtTm,
   Audit_ModifiedBy,
   Audit_OperationType,
   ReportForecastId, 
   InactiveFlag, 
   CreatedDtTm, 
   ModifiedDtTm, 
   ModifiedBy, 
   Timestamp)
  SELECT getdate(), 
   suser_name(),
   'I',
   ReportForecast.ReportForecastId, 
   ReportForecast.InactiveFlag, 
   ReportForecast.CreatedDtTm, 
   ReportForecast.ModifiedDtTm, 
   ReportForecast.ModifiedBy, 
   ReportForecast.Timestamp
  FROM ReportForecast, inserted
  WHERE ReportForecast.ReportForecastId = inserted.ReportForecastId

  SELECT @validcnt = @@rowcount, @errno = @@error
  IF @validcnt != @numrows or @errno != 0
  BEGIN
   SELECT @errno = 998
   SELECT @errmsg = 'Error writing to audit table (ReportForecast_Audit).'
   GOTO error
  END  
 END 
 
 
 
 RETURN
error:
 SELECT @errmsg = 'ReportForecast_I: ' + @errmsg
     RAISERROR @errno @errmsg
     ROLLBACK TRANSACTION
END
go
IF OBJECT_ID('dbo.ReportForecast_I') IS NOT NULL
    PRINT '<<< CREATED TRIGGER dbo.ReportForecast_I >>>'
ELSE
    PRINT '<<< FAILED CREATING TRIGGER dbo.ReportForecast_I >>>'
go

setuser
go