Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

TRIGGER

Posted on 2005-05-06
23
Medium Priority
?
806 Views
Last Modified: 2008-02-01
Any idea why MS SQL Server doesb't like this trigger?

CREATE TRIGGER newDTag
AFTER UPDATE OF STable ON SNum
REFERENCING
      OLD ROW AS old
      NEW ROW AS new
FOR EACH ROW
   UPDATE dtSubs
   SET namee = new.SNum
   WHERE  namee = old.SNum

It doesn't like the  AFTER keyword.  I have seen two textbooks and an internet reference which say that this is valid.
0
Comment
Question by:ARACK04
  • 14
  • 9
23 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13949132
I don't know what books you are reading, but here is the correct syntax for BOL:
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
        [ WITH APPEND ]
        [ NOT FOR REPLICATION ]
        AS
        [ { IF UPDATE ( column )
            [ { AND | OR } UPDATE ( column ) ]
                [ ...n ]
        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
                { comparison_operator } column_bitmask [ ...n ]
        } ]
        sql_statement [ ...n ]
    }
}
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13949158
This:
>>for BOL<<

Should have read:
from BOL

Please tell us what you are attempting to achieve.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13949165
I see what's happening: you are using Oracle SQL syntax, right?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:ARACK04
ID: 13949192
Most likely.  The book I was looking at was platformorm independant, and presents the SQL "standard" (if there is such a thing).  I am looking in other books now.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13949207
>>and presents the SQL "standard" (if there is such a thing).  <<
Not really.  Each version of SQL has its own dialect.
0
 

Author Comment

by:ARACK04
ID: 13949233
Dealing with different versions of the same langauge is really frustrating.  Here is the shell that SQL Server gives me for a trigger:

CREATE TRIGGER [TRIGGER NAME] ON [dbo]
FOR INSERT, UPDATE, DELETE
AS

How can I reference the old row and the new row?  MS SQL does not seem to like the keyword REFERENCING either.  Fortunately I am only trying to enforce referential integrity here, so the after keyword is not too important.

I would LOVE to do this with a foreign key, but the closest thing I could find with that is a cheasy diagram option, which allows you to draw arrows from one table to another and declare the primary key/foreign key combo, but does NOT allow you to set the CASCADE option.  This is frustrating!!

Any help??
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13949247
>>How can I reference the old row and the new row? <<
In general you use the logical tables Inserted and Deleted that match the structure of the table.  But it depends on the action:

FOR INSERT: The Deleted table is empty and all the new rows are in the Inserted table.
FOR UPDATE: The Deleted table contains all the old rows and the new rows are in the Inserted table.
FOR DELETE: The Deleted table contains all the old rows and the Inserted table is empty.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13949255
>>Here is the shell that SQL Server gives me for a trigger<<
Actually, not quite.  You are missing the table name.

CREATE TRIGGER [TRIGGER NAME] ON [dbo].[Table1]
FOR INSERT, UPDATE, DELETE
AS
0
 

Author Comment

by:ARACK04
ID: 13949288
>>FOR UPDATE: The Deleted table contains all the old rows and the new rows are in the Inserted table.<<
How do I reference the  'old'  and 'new'  rows

CREATE TRIGGER newT ON StationNumbers
FOR UPDATE
AS
WHEN (new.StationNumber <> old.StationNumber)
   UPDATE dtSubs
   SET mainName = new.StationNumber
   WHERE mainName = old.StationNumber

It does not like anything after WHEN
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13949321
1. What is the primary key for StationNumbers?
2. What is the relationship between StationNumbers and dtSubs?
0
 

Author Comment

by:ARACK04
ID: 13949378
StationNumber is the PK for StationNumbers

and mainName is a foreign key to StationName

I know how to write the SQL to enforce this, I just can't find a place to do it.  I think the trigger method might be easiest.  How do I reference the old row and new row like I am trying to do above.
0
 

Author Comment

by:ARACK04
ID: 13949381
bah, mainName is a foreign key to StationNumber
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13949421
>>How do I reference the old row and new row like I am trying to do above.<<
There can be one or more rows.  The old rows are all contained in the Deleted logical table.  The new rows are in the Inserted logical table.  so you have to join these tables together with dtsubs and update.  That is the reason I was asking you for the primary keys.  Unfortunately I do not understand your structure.
0
 

Author Comment

by:ARACK04
ID: 13949430
There must be a FOR EACH ROW command, isn't there?

Each value of mainName relates to a value of StationNumber.  When I update the value of StationNumber, I want to automatically update the value of the corresponding mainName.

I have been having the problem described above by doing this with FOREIGN KEY declarations, which is why I moved to triggers.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13949443
If you can be sure that you will never update more than one row at a time than the following will do what you need:

CREATE TRIGGER newT ON StationNumbers
FOR UPDATE
AS
Declare @OldStationNumber integer,        -- Change data types appropriately
        @NewStationNumber integer

IF UPDATE(StationNumber)
   BEGIN
         Select @OldStationNumber
         From   Deleted
         
         Select @NewStationNumber
         From   Inserted
         
      UPDATE      dtSubs
      SET mainName = @NewStationNumber
      WHERE mainName = @oldStationNumber
   END
   
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13949450
Let's try that again:

CREATE TRIGGER newT ON StationNumbers
FOR UPDATE
AS
Declare @OldStationNumber integer,        -- Change data types appropriately
        @NewStationNumber integer

IF UPDATE(StationNumber)
   BEGIN
        Select @OldStationNumber = StationNumber
        From   Deleted
       
        Select @NewStationNumber = StationNumber
        From   Inserted
       
     UPDATE     dtSubs
     SET mainName = @NewStationNumber
     WHERE mainName = @oldStationNumber
   END
0
 

Author Comment

by:ARACK04
ID: 13949482
Well you really seem to know what you're talking about (like most all people on this site) but are you sure there is not anything simpler like the ORALCE sytnax of

REFERENCING
   OLD ROW AS old
...
...

FOR EACH FOR
...
...

If not, do you have any idea why MS made this so difficult?

???
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13949511
I think you are ovelooking something here.  So let me try it again a different way.  There can be no concept of OLD ROW because there can be 1 or many rows affected in a trigger.  In other words, I don't recall how it was with ORACLE, but in MS SQL Server, if you update 100 rows the trigger is only fired once at the end.  So your Inserted and Deleted tables contain all the changes.

>>If not, do you have any idea why MS made this so difficult?<<
It is by design :)
0
 

Author Comment

by:ARACK04
ID: 13949537
Right, I understand that, but that is why there *should be* a FOR EACH ROW command, which loops through each updated tuple, keeping track of the old version, and the new one.  At least that is what my references tell me.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 13949544
>>Right, I understand that, but that is why there *should be* a FOR EACH ROW command<<
The closest to that is using a CURSOR and I would never recommend that.  There is no need to do "loops through each updated tuple" that is entirely too inefficient when you can do it with one single update.  But if that is the way you want to do it than look up CURSOR in BOL.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13949596
Let me give you an example using the Products table in the Northwind database.  Let's suppose you want to keep track of all the changes in quantity (UnitsInStock) in another table (ProductsAudit)

So you would create a table like this:
CREATE TABLE ProductsAudit (
      ProductID int NOT NULL ,
      Quantity smallint NOT NULL)

And populate it with all the current values as follows:
insert      ProductsAudit (ProductID, Quantity)
Select      ProductID,
      UnitsInStock
From      Products

Now every time there is modification to the UnitsInStock the Trigger should fire and save the old value to the ProductsAudit table. So your Trigger would look like this:

CREATE TRIGGER trg_Products ON dbo.Products

FOR UPDATE

AS

SET NOCOUNT ON

IF UPDATE(UnitsInStock)    -- Only update when UnitsInStock changes
   Begin
      Update      ProductsAudit
      Set Quantity = d.UnitsInStock
      From      ProductsAudit p
            Inner Join Deleted d On p.ProductID = d.ProductID
   End

Now, it does not matter if you update one product:
Update Products Set UnitsInStock = 10 Where ProductID = 1

Or many:
Update Products set UnitsInStock = UnitsInStock + 1

They will all be updated in the ProductsAudit table, without the need of cycling through the rows.

This may not be a very useful example as the ProductsAudit table will only contain the last value.  But I hope it illustrates how the Deleted and Inserted logical tables work.
0
 

Author Comment

by:ARACK04
ID: 13949718
I am at least correct that the

FOR EACH ROW  command exists for Oracle thought, right?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13950961
Yes.  Here is an example from MSDN comparing an Oracle trigger with a MS SQL Server Trigger (excuse the formatting that is the way it was presented):

CREATE TRIGGER STUDENT_ADMIN.TRACK_GRADES
AFTER
INSERT OR UPDATE OR DELETE
ON STUDENT_ADMIN.GRADE
FOR EACH ROW
BEGIN
INSERT INTO GRADE_HISTORY(
TABLE_USER, ACTION_DATE,
OLD_SSN, OLD_CCODE,
OLD_GRADE, NEW_SSN,
NEW_CCODE, NEW_GRADE)
VALUES (USER, SYSDATE,
:OLD.SSN, :OLD.CCODE, :OLD.GRADE, :NEW.SSN, :NEW.CCODE, :NEW.GRADE),
END;

CREATE TRIGGER STUDENT_ADMIN.TRACK_GRADES
ON STUDENT_ADMIN.GRADE
FOR INSERT, UPDATE, DELETE
AS
INSERT INTO GRADE_HISTORY(
TABLE_USER, ACTION_DATE,
OLD_SSN, OLD_CCODE, OLD_GRADE
NEW_SSN, NEW_CCODE, NEW_GRADE)
SELECT USER, GETDATE(),
OLD.SSN, OLD.CCODE, OLD.GRADE,
NEW.SSN, NEW.CCODE, NEW.GRADE
FROM INSERTED NEW FULL OUTER JOIN
DELETED OLD ON NEW.SSN = OLD.SSN

For a full explanation of all the differences, here is the link:
http://www.microsoft.com/resources/documentation/sql/7/all/reskit/en-us/part11/sqc17.mspx

Look for the section on Triggers.

It also, goes to some length explaining the Inserted and Deleted logical tables, hopefully that should be a better explanation than mine.

Caveat:  This refers to MS SQL Server 7 and since then "AFTER Triggers" have been added, but you should get an idea.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question