what's the equivalent to the NEW keyword in T-SQL

[rewdee]

I have a simple oracle script that checks a field and modifies corresponding dependent fields on a specified value:

GO
CREATE  TRIGGER [Check_Dependent_FLDS] ON [dbo].[GRP_BENEFITS_DENTAL] 
FOR INSERT, UPDATE
AS
/*Check whether columns  3 or 6 has been updated. If any or all of columns 3 or 6 have been changed, 0 the dependent fields
  Field 3: DentalDeduct with dependent fields DeductFam (FieldNo. 4) and DeductSing (FieldNo. 5)
  Field 6: CoInsOpt with dependent field CoInsPercent (FieldNo. 7*/
  
  IF new.DentalDeduct='F' Then
  Begin
    new.DeductFam:=0;
    new.DeductSing:=0;
  end;

  IF new.CoInsOpt='F' new.CoInsPercent:=0;

GO

What is the equivalent of the PL\SQL 'new' key word in T-SQL?

Thanks,
Rewdee

 

[SQLSister]

I don't know for sure what the new keyword does in Oracle, but I'm guessing you want to see what the new value of the data is? In SQL Server, there are two pseudotables which are accessible only from triggers, one called inserted and one called deleted. You use these in triggers to check the values which were inserted, changed or deleted. So insert trigger would have values only in the inserted table, an update trigger would have values in both, deleted containing the original valued and inserted containing the new ones, and a deleted triger would only have values in the deleted table. You use these just like you would use any other table in the SQL statement.

Hope that helps.

 

[rewdee]

Thanks for the response SQLSister.

How do I use the inserted table. I tried the following:

DECLARE depFlds_cursor CURSOR FOR
SELECT DentalDeduct, CoInsOp,DentalID FROM Inserted
...

When I checked the syntax I got an invalid column names error?

Thanks,
Rewdee

 

[SQLSister]

That use of the select with the inserted table should work. It will only work in a trigger though, so if you are in Query analyser trying to test the code before you create a trigger, it wouldn't work becaue inserted isn't avaliable unless you are running a trigger. And are you sure that those are the correct column names, sometimes it's the little things that trip us up.

BTW cursors are to be avoided in SQL Server. They hurt performance tremendously. Use an update statement that joins to inserted and uses additional where clauses to identify what to change instead. Something like:
UPDATE SalesRecords
SET TotalSalePrice = inserted.SalePrice * inserted.Qty
FROM SalesRecords, inserted
WHERE SalesRecords.GUID = inserted.GUID
and (Inserted.Qty <>SalesRecords.Qty Or Inserted.SalePrice<>SalesRecords.SalePrice)

I've never used a cursor from within a trigger, so possibly your problem has something to do with that, but I think they are allowed in triggers so I don't know why you are getting the error you are getting.

 

[rewdee]

Embarassing or what??? I misspelled a column name.
Finally finished with this:

CREATE  TRIGGER [Check_Dependent_FLDS] ON [dbo].[GRP_BENEFITS_DENTAL] 
FOR INSERT, UPDATE
AS
  
  DECLARE @dentalDeduct CHAR(1)
  DECLARE @CoInsPerc     Char(1)

  SELECT @dentalDeduct = DentalDeduct FROM inserted
  SELECT @CoInsPerc=CoInsPercent FROM inserted

  IF @dentalDeduct='F' 
  begin
      UPDATE GRP_BENEFITS_DENTAL
      SET DeductFam =0,DeductSing=0
      FROM  GRP_BENEFITS_DENTAL, inserted
      WHERE  GRP_BENEFITS_DENTAL.DentalID =inserted.DentalID
  end
      
  IF @CoInsPerc='F' 
     UPDATE GRP_BENEFITS_DENTAL
      SET CoInsPercent =0
      FROM  GRP_BENEFITS_DENTAL, inserted
      WHERE  GRP_BENEFITS_DENTAL.DentalID =inserted.DentalID

Thanks,
Rewdee

 

[SQLSister]

Hey Rewdee, it happens to us all. I've learned to drag my column names over from the object browser, that way, my lousy typing never catches me out on object names.