Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
T-SQL Trigger/SQL Server 7.0
Message
From
04/07/2002 12:30:14
 
 
To
03/07/2002 12:41:04
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Miscellaneous
Thread ID:
00672425
Message ID:
00675374
Views:
25
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
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform