Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need help with the trigger

Author  Topic 

zspeed01
Starting Member

6 Posts

Posted - 2013-04-28 : 09:48:32

Hello everyone, I can really use some help with this problem. I suppose to create a trigger that prevents records from being deleted on Sundays. I'm not sure what I'm missing here.



This is what I got so far:


CREATE TABLE MyTable
(FirstName nvarchar(50), LastName nvarchar(50))
insert into MyTable (FirstName, LastName) values ('First1', 'Last1')
insert into MyTable (FirstName, LastName) values ('First2', 'Last2')
insert into MyTable (FirstName, LastName) values ('First3', 'Last3')

CREATE TRIGGER tr_NoDelete
ON MyTable
AFTER DELETE
AS
BEGIN
IF EXISTS(SELECT * FROM tr_NoDelete WHERE GETDATE() = 'Sunday')
BEGIN
RAISERROR ('You cannot delete records on Sunday', 16, 1)
ROLLBACK TRAN
END
END
GO

DELETE FROM MyTable
delete from MyTable where FirstName = 'First1'


ERROR MESSAGE:
Msg 208, Level 16, State 3, Procedure tr_NoDelete, Line 6
Invalid object name 'tr_NoDelete'.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-28 : 10:35:36
If I understood you correctly - that is, you do not want anyone to delete records from the table MyTable on Sundays - then what you need is something like this:
CREATE TRIGGER tr_NoDelete
ON MyTable
AFTER DELETE
AS
BEGIN
if (datediff(dd,0,getdate())%7 = 6) -- checks if today is a Sunday
BEGIN
ROLLBACK TRAN
RAISERROR ('You cannot delete records on Sunday', 16, 1)
END
END
GO
Go to Top of Page

zspeed01
Starting Member

6 Posts

Posted - 2013-04-28 : 10:41:11
I supposed to use IF EXISTS statement, but this works as well. Thank you very much for your help
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-28 : 18:11:49
IF EXISTS is used to check for the existence of something - for example, the existence of a any rows in a table that meet certain criteria. What you want to do logically is different from that - you are not trying to check whether SOMETHING EXISTS, you are trying to check whether SOMETHING IS (and that SOMETHING being current day). I suppose you could go to great lengths to use IF EXISTS like shown below, but why would you want to?
if exists ( SELECT * FROM 
(select case when (datediff(dd,0,getdate())%7 = 6) then 1 end as IsSunday) s
WHERE IsSunday is not null )
Go to Top of Page
   

- Advertisement -