Thanks for the help steve and Barry !!!

well my task was to script the SQl for triggers in the same format what I written for a table initially . so I used cursors to do this and I did accomplished it

and here is the code

DECLARE Table_Trigger CURSOR

FOR

SELECT name TableName FROM sys.tables

DECLARE @table_name SYSNAME

DECLARE @Primary_key SYSNAME

DECLARE @Trigger VARCHAR(MAX)

-- Loop through All tables

OPEN Table_Trigger

FETCH NEXT FROM Table_Trigger INTO @table_name

WHILE (@@FETCH_STATUS = 0)

BEGIN

SET NOCOUNT ON

SELECT @Primary_key = COL_NAME(ic.OBJECT_ID,ic.column_id)

FROM sys.indexes AS i

INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id

WHERE i.is_primary_key = 1 and i.object_id = Object_id(@table_name)

SET @Trigger = '-------------------'+@table_name+'

Create TRIGGER [TR_UD_'+@table_name+'] ON '+@table_name+

'

INSTEAD OF DELETE, UPDATE

AS

DECLARE @ErrorMessage VARCHAR(4000)

DECLARE @ErrorSeverity INT

DECLARE @ErrorState INT

BEGIN TRY

INSERT INTO z_'+@table_name+'

([ActionDML],'

-- Loop through All Columns for a table

DECLARE Table_Column_Tri CURSOR

FOR

SELECT column_name

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @table_name

DECLARE @column_name SYSNAME

DECLARE @Delete VARCHAR(MAX)

DECLARE @select VARCHAR(MAX)

DECLARE @Insert VARCHAR(MAX)

DECLARE @Pos int SET @Pos = 1

OPEN Table_Column_Tri

FETCH NEXT FROM Table_Column_Tri INTO @column_name

WHILE (@@FETCH_STATUS = 0)

BEGIN

IF (@select IS NULL And @Pos = 1)

SET @select = QUOTENAME(@column_name)

ELSE

SET @select = @select + ' , ' +QUOTENAME(@column_name)

IF (@Delete IS NULL)

SET @Delete = 'Deleted.'+ QUOTENAME(@column_name)

ELSE

SET @Delete = @Delete + ' , ' + 'Deleted.'+ QUOTENAME(@column_name)

IF (@Insert IS NULL AND @column_name <> @Primary_key)

SET @insert = + QUOTENAME(@column_name)+ ' = inserted.'+QUOTENAME(@column_name)

ELSE

SET @insert = @insert + ' , ' + QUOTENAME(@column_name)+ ' = inserted.'+QUOTENAME(@column_name)

FETCH NEXT FROM Table_Column_Tri INTO @column_name

END

CLOSE Table_Column_Tri

DEALLOCATE Table_Column_Tri

SET @TRIGGER = @TRIGGER +@select+')

SELECT CASE WHEN deleted.'

+@Primary_key+' IS NOT NULL AND inserted.'+@Primary_key+' IS NOT NULL

THEN CASE WHEN deleted.ROWVERSION = -1 THEN '+''''+'M'+''''+'

WHEN deleted.ROWVERSION = 0 THEN '+''''+'I'+''''+'

ELSE '+''''+'U'+''''+'

END

WHEN deleted.'+@Primary_key+' IS NOT NULL AND inserted.'+@Primary_key+' IS NULL

THEN '+''''+'D'+''''+'

END as ActionDML,

'+@Delete+'

FROM deleted

LEFT JOIN '+@table_name+' on '+@table_name+'.'+@Primary_key+' = deleted.'+@Primary_key+'

LEFT JOIN inserted on '+@table_name+'.'+@Primary_key+' = inserted.'+@Primary_key+'

BEGIN

IF (SELECT COUNT(*) FROM inserted) = 0

Delete '+@table_name+' where '+@Primary_key+' in (select '+@Primary_key+' from deleted)

ELSE

UPDATE '+@table_name+' set '+@Insert+'

FROM '+@table_name+', inserted

WHERE '+@table_name+'.'+@Primary_key+' = inserted.'+@Primary_key+'

END

END TRY

BEGIN CATCH

SELECT @ErrorMessage = '+''''+'TR_UD_'+@table_name+':'+''''+'+ ERROR_MESSAGE(),

@ErrorSeverity = ERROR_SEVERITY(),

@ErrorState = ERROR_STATE();

IF @@TRANCOUNT > 0

BEGIN

RAISERROR (@ErrorMessage, 16,1) with nowait

ROLLBACK TRAN

RETURN

END

END CATCH'

---Print Trigger

SET @Delete = Null

SET @select = Null

SET @Insert = Null

PRINT @TRIGGER

PRINT ''

FETCH NEXT FROM Table_Trigger INTO @table_name

END

CLOSE Table_Trigger

DEALLOCATE Table_Trigger