Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
T-SQL Trigger/SQL Server 7.0
Message
De
04/07/2002 12:30:14
 
 
À
03/07/2002 12:41:04
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Divers
Thread ID:
00672425
Message ID:
00675374
Vues:
24
Since you're not really using #temptable maybe the two can be combined using a derived table. Also, there's better performance if you create the temp. table and then fill it, instead of using the SELECT INTO configuration.

SELECT
CustomerId
INTO #TempTable2
FROM (
SELECT del.CustomerID,del.CompanyName,del.ContactName,del.ContactTitle,
del.Address,del.City,del.Region,del.PostalCode,del.Country,del.Phone,del.Fax
FROM deleted del
UNION
SELECT ins.CustomerID,ins.CompanyName,ins.ContactName,ins.ContactTitle,
ins.Address,ins.City,ins.Region,ins.PostalCode,ins.Country,ins.Phone,ins.Fax
FROM inserted ins ) AS x
GROUP BY
customerId
HAVING
COUNT(customerId) > 1

I also think you should look at look at removing the temp table all together
by using the above as a subquery of the other two INSERT statements:

INSERT INTO CUSTOMERS_AUDIT (CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax,cTrnsTyp)
SELECT del.CustomerID,del.CompanyName,del.ContactName,del.ContactTitle,del.Address,del.City,del.Region,del.PostalCode,del.Country,del.Phone,del.Fax,'U' FROM deleted del
WHERE CustomerId IN (

SELECT
CustomerId
INTO #TempTable2
FROM (
SELECT del.CustomerID,del.CompanyName,del.ContactName,del.ContactTitle,
del.Address,del.City,del.Region,del.PostalCode,del.Country,del.Phone,del.Fax
FROM deleted del
UNION
SELECT ins.CustomerID,ins.CompanyName,ins.ContactName,ins.ContactTitle,
ins.Address,ins.City,ins.Region,ins.PostalCode,ins.Country,ins.Phone,ins.Fax
FROM inserted ins ) AS x
GROUP BY
customerId
HAVING
COUNT(customerId) > 1

)


-Mike
Michael Levy
MCSD, MCDBA
ma_levy@hotmail.com
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform