CREATE TRIGGER [dbo].[AddAvatarToContract]
ON  [dbo].[Contract]
AFTER INSERT
AS
BEGIN
-- =============================================
-- Author:		Bokor J. Márk
-- Adapted:		dr. Dózsa András
-- Create date: 2014.08.06
-- Description:	General insert trigger
--              to assign avatarId
-- =============================================
SET NOCOUNT ON
--work wariables
DECLARE @NewAvatarID Int
DECLARE @InsertedId Int
DECLARE @TriggeredTableId Int
DECLARE @TriggeredTableName NVARCHAR(255)
DECLARE @TriggerSysID Int
DECLARE @TableSysID Int
DECLARE @tmpName as nvarchar(255)
DECLARE @tmpKey as int
DECLARE @tERR as nvarchar(512)

--query our trigger's id
SET @TriggerSysID = @@PROCID
--with the triggers id, we can fetch its parent table's id
SELECT @TableSysID = parent_id FROM sys.triggers  WHERE object_id = @TriggerSysID
--, and name. With this technique we can reuse this trigger without modification
-- in any database, where Id and AvatarId columns are present.
SELECT @TriggeredTableName = OBJECT_NAME(@TableSysID)
--query the local id of the triggered table
SELECT @TriggeredTableId = Id FROM TableType WHERE Name = @TriggeredTableName
--update the just inserted record's AvatarID column with this newly created avatarId

DECLARE tmpCursor CURSOR FOR
SELECT Id, AvatarId FROM Inserted ORDER BY Id
OPEN tmpCursor
FETCH NEXT FROM tmpCursor INTO @InsertedId, @NewAvatarID
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @NewAvatarID IS NULL
BEGIN
--use this id, to generate an avatar for our inserted record
INSERT INTO [dbo].[Avatar] ([TableTypeId]) VALUES (@TriggeredTableId)
SELECT @NewAvatarID = SCOPE_IDENTITY()
--store the new id
EXECUTE dbo.sp_AddAvatarIdOnInsert @TriggeredTableName, N'Id', @NewAvatarID, @InsertedId
END
ELSE --we have an avatarid in a new rec?!
BEGIN
EXEC sp_FindAvatarId @TriggeredTableName, @InsertedId, @NewAvatarID, @tmpName output , @tmpKey output
IF @tmpName = @TriggeredTableName
BEGIN
IF @tmpKey = -1
BEGIN
SET IDENTITY_INSERT [dbo].[Avatar] ON
INSERT INTO [dbo].[Avatar] ([Id], [TableTypeId]) VALUES (@NewAvatarID, @TriggeredTableId)
SET IDENTITY_INSERT [dbo].[Avatar] OFF
END
ELSE IF @tmpKey > 0
BEGIN
SET @tERR = 'The given AvatarId is being used by another record! Table: ' + @tmpName + ' Id: ' + @tmpKey
RAISERROR(@tERR,11,1)
END
END
ELSE
BEGIN
SET @tERR = 'The given AvatarId is being used by another record! Table: ' + @tmpName + ' Id: ' + @tmpKey
RAISERROR(@tERR,11,1)
END
END
-- get next record
FETCH NEXT FROM tmpCursor INTO @InsertedId, @NewAvatarID
END
CLOSE tmpCursor
DEALLOCATE tmpCursor
END