SET NOCOUNT ON
GO

USE Northwind
GO

CREATE TABLE myTable99(Col1 int IDENTITY(1,1) PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col2) SELECT 'x' UNION ALL SELECT 'y' UNION ALL SELECT 'z'
GO

SELECT * FROM myTable99
GO

USE Pubs
GO

CREATE TABLE myTable00(Col0 int IDENTITY(1,1) PRIMARY KEY, Col1 int, Col2 char(1))
GO

CREATE TRIGGER myTrigger00 ON myTable00 FOR INSERT, UPDATE
AS
  BEGIN
	SET NOCOUNT ON
	DECLARE @Error_Message varchar(100)
	IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
		IF EXISTS(SELECT * FROM inserted i
			   WHERE NOT EXISTS(SELECT * 
					      FROM Northwind.dbo.myTable99 t
					     WHERE i.Col1 = t.Col1))
		  BEGIN
			SET @Error_Message = 'Foreign Key Constraint violation for INSERT Against Northwind.dbo.myTable99'
			GOTO Trigger_Error
		  END

	IF EXISTS(SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
		IF EXISTS(SELECT * FROM inserted i
			   WHERE NOT EXISTS(SELECT * 
					      FROM Northwind.dbo.myTable99 t
					     WHERE i.Col1 = t.Col1))
		  BEGIN
			SET @Error_Message = 'Foreign Key Constraint violation for UPDATE Against Northwind.dbo.myTable99'
			GOTO Trigger_Error
		  END

	Trigger_Exit:
		SET NOCOUNT OFF
		Return

	Trigger_Error:
		ROLLBACK TRAN
		RAISERROR 50001 @Error_Message
		GOTO Trigger_Exit
  END
GO

USE Northwind
GO
CREATE TRIGGER myTrigger99 ON myTable99 FOR DELETE
AS
  BEGIN
	SET NOCOUNT ON
	DECLARE @Error_Message varchar(100)
	IF NOT EXISTS(SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
		IF EXISTS(SELECT * FROM deleted i
			   WHERE EXISTS(SELECT * 
					  FROM Pubs.dbo.myTable00 t
					     WHERE i.Col1 = t.Col1))
		  BEGIN
			SET @Error_Message = 'Foreign Key Constraint violation for DELETE Against Pubs.dbo.myTable00'
			GOTO Trigger_Error
		  END

	Trigger_Exit:
		SET NOCOUNT OFF
		Return

	Trigger_Error:
		ROLLBACK TRAN
		RAISERROR 50001 @Error_Message
		GOTO Trigger_Exit
  END
GO

USE Pubs
GO
			   
INSERT INTO myTable00(Col1, Col2) SELECT 1,'x'
GO

SELECT * FROM myTable00
GO

INSERT INTO myTable00(Col1, Col2) SELECT 4,'a'
GO
SELECT * FROM myTable00
GO

UPDATE myTable00 SET Col1 = 4 WHERE Col1 = 1
GO
SELECT * FROM myTable00
GO

USE Northwind
GO

DELETE FROM myTable99 WHERE Col1 = 1
GO

SELECT * FROM myTable99
GO
	   
USE Pubs
GO
DROP TRIGGER myTrigger00
DROP TABLE myTable00
GO


USE Northwind
GO
DROP TABLE myTable99
SET NOCOUNT OFF
GO