Would it introduce any issues in the system or not depends on implementation.However, it would always lead to unnecessary performance overhead. The code below creates another table and changes trigger in a way, that mimics behavior of audit functional, which is frequently implemented based on triggers. Audit ( ID int not null identity(1,1), On Date datetime2(0) not null constraint DEF_Audit_On Date default getutcdate(), Message varchar(64) not null, ) go alter trigger trg Data_AI on dbo.
Audit(Message) values(@Msg); end should be the second action trigger does.
Without that SQL Server returns affected number of rows for each operator in the trigger in addition to original DML statement.
Some client libraries rely on the single message in the output and would not work correctly in case of multiple messages.
triggers introduce fragmentation and increase tempdb load due to version store usage.
Triggers are running in context of transaction, increase the time in which locks are held and contribute to locking and blocking in the system.
However, in some cases, we have to use triggers and it is important to develop them correctly.
SQL Server fires triggers on statement rather than row level.
For example, if update statement modified 10 rows, create table dbo.
Data ( ID int not null, Value varchar(32) ) go create trigger trg Data_AI on dbo.
Data after insert as /* Some code */ declare @ID int set @ID = (select ID from inserted) /* Some code */ And there is another consequence of statement-level nature of triggers.
They fire even if DML statement did not modify any rows.