March 17, 2005 at 10:43 am
Hi,
I see that when reading examples the word 'Inserted' or 'deleted' is used to reference the recordset. Plus I see table reference "i" like 'i.fieldname', WHAT is this, cant find anything in this on BOL, maybe I am searching wrong, were can I find info on this ?
example
-- Create the trigger.
CREATE TRIGGER tr_tTable_Alter_insert
ON dbo.tTable_Alter
FOR INSERT
as
IF (SELECT COUNT(*) FROM INSERTED
WHERE intNameID > 100) > 0
BEGIN
PRINT 'Error: Your authority does not allow you to insert a manager into the table'
ROLLBACK TRANSACTION
END
GO
March 17, 2005 at 11:06 am
In BOL, type "triggers" in the Index, and open the section titled "inserted tables":
Two special tables are used in trigger statements: the deleted table and the inserted table. Microsoft® SQL Server™ 2000 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for trigger actions; however, you cannot alter the data in the tables directly.
March 17, 2005 at 11:11 am
The "i" reference is a table alias. It's a usefull shorthand, particularly when dealing with long table names.
For example, the following query:
SELECT tblProductsWebSetting.SKU, tblProductsWebSetting.Title, tblProductsBibliographicInfo.Author FROM tblProductsWebSetting LEFT JOIN tblProductsBilbiographicInfo ON tblProductsWebSetting.SKU = tblProductsBilbiographicInfo.SKU
can be much easier written as:
SELECT ws.SKU, ws.Title, bi.Author FROM tblProductsWebSetting AS "ws" LEFT JOIN tblProductsBilbiographicInfo AS "bi" ON ws.SKU = bi.SKU
(BTW, the AS and quotes are not necessary. I usually don't use them, i.e. FROM tblProductsWebSetting ws)
It's very handy. I use it constantly.
HTH!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply