January 18, 2006 at 12:56 am
Hi, I have the following Trigger Code:
CREATE TRIGGER [S1_Update] ON [dbo].[Spender]
FOR UPDATE
AS
if update (empf_id)
select * from inserted
INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)
(Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user, 'UPDATE' as Art from inserted)
if update ([Name])
select * from inserted
INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)
(Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user, 'UPDATE' as Art from inserted)
if update (Vorname)
select * from inserted
INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)
(Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user, 'UPDATE' as Art from inserted)
If update (telefon)
select * from inserted
INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)
(Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user, 'UPDATE' as Art from inserted)
If update ([str])
select * from inserted
INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)
(Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user, 'UPDATE' as Art from inserted)
If update (land)
select * from inserted
INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)
(Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user, 'UPDATE' as Art from inserted)
If update(plz)
select * from inserted
INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)
(Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user, 'UPDATE' as Art from inserted)
if update (ort)
select * from inserted
INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)
(Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user, 'UPDATE' as Art from inserted)
If update (bezirk)
select * from inserted
INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)
(Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user, 'UPDATE' as Art from inserted)
If update (anrede)
select * from inserted
INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)
(Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user, 'UPDATE' as Art from inserted)
If update (kto)
select * from inserted
INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)
(Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user, 'UPDATE' as Art from inserted)
If update(blz)
select * from inserted
INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)
(Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user, 'UPDATE' as Art from inserted)
if update (Inaktiv)
select * from inserted
INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)
(Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user, 'UPDATE' as Art from inserted)
if update (gebdat)
select * from inserted
INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)
(Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user, 'UPDATE' as Art from inserted)
That trigger works very slow . The table has 535286 rows. What can I do ?
Thanks for help !
Markus
January 18, 2006 at 1:48 am
Hi
partition the table into smaller tables which
contains the rows you need and then apply the trigger.
Try this ..and please reply..
regards,
jyothirmayee.
January 18, 2006 at 2:08 am
hi ! thanks. it works now.
January 18, 2006 at 2:21 am
As written every time it runs it does one or more selects from inserted and 14 inserts
If update(blz)
select * from inserted
INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)
(Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user, 'UPDATE' as Art from inserted)
There's no BEGIN...END block so the if only applies to the first select. The insert into will always run.
Why are you doing the first select * from inserted?
I think this should probably read
IF UPDATE(blz)
BEGIN
SELECT * FROM inserted --what's this for?
INSERT INTO ....
END
How many rows are you updating at a time?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2006 at 4:12 am
i'd also advise the following changes
if update (Inaktiv)
begin
if (select Inaktiv from inserted)(select Inaktiv from deleted)
begin
INSERT INTO ....
end
end
that way your trigegr doesn't fire when you update a row but set it's value to the previous value - e.g update mytable set myfield=myfield
some apps (mainly those using stored procedures) perform an update of every field in a table regardless of whether they have changed or not - this will fire your trigger every time - even though the data doesn't appear to change.
MVDBA
January 18, 2006 at 4:21 am
That's not going to work very well for a multiple row update.
Perhaps something more like
Insert into <some tbl> (<field list> )
SELECT <fields>
FROM inserted inner join deleted ON inserted.<primary key field>=deleted.<primary key field>
WHERE inserted.Inaktiv != deleted.Inaktiv
This does assume that the pk doesn't change.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2006 at 4:45 am
agreed
MVDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply