#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    2

    Creating Trigger, New To SQL Server Managment


    I'm creating a trigger, that prevents deletion of a row in the Installation Table by catching a Delete request and instead of "ing" it, to update the column Uninstall and Uninstall Date, with uninstall bit value of 1 and the current date. However, I cannot get the trigger to select only the row being deleted. It actually updates all the rows with these values, when I need it to only effect the row being deleted.

    The Columns in the table are
    InstallKey - Uninstall - UninstallDate

    Trigger:

    Code:
    ALTER TRIGGER [dbo].[tr_Uninstall] ON [dbo].[Installation]
    INSTEAD OF DELETE AS DECLARE @Uninstall bit
    DECLARE @UninstallDate datetime
    DECLARE @InstallKey NCHAR(10)
    SELECT @Uninstall = Uninstall FROM Inserted
    SELECT @UninstallDate = UninstallDate FROM Inserted
    SELECT @InstallKey = InstallKey FROM Inserted
    WHERE InstallKey = @InstallKey
    BEGIN
    UPDATE Installation
    SET Uninstall = 1, UninstallDate = GETDATE()
     Print 'The installation has been marked as Uninstalled' END
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    2
    Nevermind, Im new but I figured it out:

    Code:
    ALTER TRIGGER [dbo].[tr_Uninstall] ON [dbo].[Installation]
    INSTEAD OF DELETE AS
    DECLARE @Uninstall bit
    DECLARE @UninstallDate datetime
    DECLARE @InstallKey NCHAR(10)
    SELECT @Uninstall = Uninstall FROM Deleted
    SELECT @UninstallDate = UninstallDate FROM Deleted
    SELECT @InstallKey = InstallKey FROM Deleted
    BEGIN
    UPDATE Installation SET Uninstall = 1, UninstallDate = GETDATE()
    WHERE @InstallKey = InstallKey
    Print 'The installation has been marked as Uninstalled' END
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    7
    Rep Power
    0
    Originally Posted by BitZoid
    Nevermind, Im new but I figured it out:

    Code:
    ALTER TRIGGER [dbo].[tr_Uninstall] ON [dbo].[Installation]
    INSTEAD OF DELETE AS
    DECLARE @Uninstall bit
    DECLARE @UninstallDate datetime
    DECLARE @InstallKey NCHAR(10)
    SELECT @Uninstall = Uninstall FROM Deleted
    SELECT @UninstallDate = UninstallDate FROM Deleted
    SELECT @InstallKey = InstallKey FROM Deleted
    BEGIN
    UPDATE Installation SET Uninstall = 1, UninstallDate = GETDATE()
    WHERE @InstallKey = InstallKey
    Print 'The installation has been marked as Uninstalled' END
    one thing you should keep bare in mind, that triggers on SQL Server works differently from Oracle. Triggers on SQL Server works as set-based while Oracle's can act as set-based or row-based. But often, DBA creating triggers in Oracle as row-based.
    So,...as an "short" alternative for your trigger, you can create as below:
    Code:
    alter trigger tid_uninstall on installation
    instead of delete as
    begin
    	update installation
    		set uninstall=1, UninstallDate=getdate()
    		from installation join deleted on installation.InstallKey=deleted.InstallKey;
    	print 'DONE.';
    end;
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    Originally Posted by speedtrap
    one thing you should keep bare in mind...

    [offtopic]

    bear in mind = remember

    keep bare in mind = think about porn



    [/offtopic]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    7
    Rep Power
    0
    Originally Posted by r937

    [offtopic]

    bear in mind = remember

    keep bare in mind = think about porn



    [/offtopic]
    wkwkwkwkwkkw....my mistake. I just realized I made typo in this thread. as correction: "...keep bear in mind..." Thanks r937.

IMN logo majestic logo threadwatch logo seochat tools logo