Trigger works not correctly

1

Hello

i wrote the following trigger on my table
but it doesn't work correctly and when i update fehlerort1 to a value that doesn't begin with a again it fires. can you help me please?
thank you

ALTER TRIGGER [dbo].[trigger1]
ON [dbo].[table11]
instead of update
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @count int
declare @orta varchar(50)
declare @ortb varchar(50)
select @count= auftrag from deleted
select @orta= fehlerort1 from deleted
select @ortb= fehlerort1 from inserted

if LEFT (@ortb,1)='a'
begin
update [dbo].[table11] set fehlerort1=@orta where auftrag =@count
print 'it is valid'
end
END

2

You have a recursive trigger, see here

Also, your trigger assumes one row is being changed by the triggering update. What if there are 2, 10, 1000?

3

Hello

I can't understand it
Can you explain more please?
Thank you
What are 2,10,1000?

4

what if some writes an update statement that updates 2, 10, or 1000 rows at once?

5

Triggers in SQL Server only fire once per statement -- DELETE or INSERT or UDPATE -- not once for every row. Thus, if you update 100 rows in one statement, the update trigger only runs once, but it receives 100 rows in the inserted and deleted tables. You have to process all the rows at once.

My best guess of what you need is below. Adjust it as needed. You could add the PRINT statement back in if you really want to, at least for testing, but you're technically not supposed to return any data from a trigger.

ALTER TRIGGER [dbo].[trigger1] 
ON [dbo].[table11]
AFTER UPDATE
AS 
SET NOCOUNT ON;
UPDATE t11
SET fehlerort1 = d.fehlerort1
FROM table11 t11
INNER JOIN inserted i ON i.auftrag = t11.auftrag
INNER JOIN deleted d ON d.auftrag = t11.auftrag
WHERE 
    i.fehlerort1 LIKE 'a%'
IF @@ROWCOUNT > 0
    PRINT 'es ist gut'
GO --end of trigger