Inside a trigger, I want to not only get the previous values of the rows that were changed (DELETED table) but also the values of the rows that weren't changed at all.
Would this work?
SELECT * FROM DELETED
UNION
SELECT * FROM Table WHERE Table.Id NOT IN (SELECT Id FROM DELETED)
You say
I'm trying to compare how many rows there are in the table before the trigger and after the trigger
You don't actually need to query the whole table, which is very inefficient. You can just compare inserted
and deleted
DECLARE @diff bigint =
(SELECT COUNT_BIG(*) FROM inserted)
- (SELECT COUNT_BIG(*) FROM deleted);
This works in a trigger which handles all of INSERT, UPDATE, DELETE
.
If you have just a INSERT
or UPDATE
trigger you only need to query inserted
.
DECLARE @diff bigint = (SELECT COUNT_BIG(*) FROM inserted);
If you have just a DELETE
trigger you only need to query deleted
.
DECLARE @diff bigint = (SELECT COUNT_BIG(*) FROM deleted);
If you need the full amouont of rows in the table, the fastest (if not always perfectly accurate) way of doing it is:
DECLARE @TotalRows bigint = (
SELECT SUM(st.row_count)
FROM sys.dm_db_partition_stats st
JOIN sys.tables t ON t.object_id = st.object_id
JOIN sys.schemas s ON s.schame_id = t.schema_id
WHERE t.name = 'MyBigtable'
AND s.name = 'dbo'
AND st.index_id < 2
);
This result is after changes have been applied, so subtract the previous result to get the before value.