/*
  Illustrates the firing of a trigger for
	the insertion of a customer row with a non-existing repnum value.
  Replace "FOR" with "INSTEAD OF" and
	observe the integrity constraint executions

  Create a mail profile in SQL Server as below:
    Managment Studio > Management > Database Mail > Configure Database Mail >
	Outgoing mail server:
	  your email adds and name
	  smtp.cs.fiu.edu
	  uncheck the SSL connection
	Basic authentication: SQL username and password

  --Prabakar 3/1/10
*/
create trigger RepCheck
on Customer
--FOR insert as -- use (either "FOR" or "AFTER") or "INSTEAD OF"
INSTEAD OF insert as
-- body of the trigger
if not exists
	(select RepNum
	 from inserted -- newly inserted row
	 where RepNum IN
	  ( select RepNum from Rep))
 Begin
  Declare @message varchar(30)

  --print('Invalid Rep Number')
  raiserror('Invalid Rep Number', 0, 1)

  set @message = 'Incorrect rep number ' 

  EXEC msdb.dbo.sp_send_dbmail
	    @profile_name = 'ClassProfile',
	    @recipients = 'GemMine@gmail.com',
            @subject = 'Premiere part price change - Warning',
            @body = @message,
            @body_format = 'HTML' ;
  -- Rollback -- undo the new insertion
 End
