Creating Trigger, New To SQL Server Managment

Dev Shed Forums Sponsor:

November 5th, 2012, 09:29 AM

BitZoid

Contributing User

Join Date: Nov 2012

Posts: 98

Time spent in forums: 1 Day 2 h 20 m 38 sec
Reputation Power: 1

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

November 5th, 2012, 09:50 AM

BitZoid

Contributing User

Join Date: Nov 2012

Posts: 98

Time spent in forums: 1 Day 2 h 20 m 38 sec
Reputation Power: 1

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

January 10th, 2013, 03:10 AM

speedtrap

Registered User

Join Date: Dec 2011

Posts: 7

Time spent in forums: 3 h 30 m 2 sec
Reputation Power: 0

Quote:

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;