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.
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_NoDeleteON MyTableAFTER DELETEAS BEGIN IF EXISTS(SELECT * FROM tr_NoDelete WHERE GETDATE() = 'Sunday') BEGIN RAISERROR ('You cannot delete records on Sunday', 16, 1) ROLLBACK TRAN END ENDGODELETE FROM MyTabledelete from MyTable where FirstName = 'First1'ERROR MESSAGE:Msg 208, Level 16, State 3, Procedure tr_NoDelete, Line 6Invalid 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 DELETEASBEGINif (datediff(dd,0,getdate())%7 = 6) -- checks if today is a SundayBEGIN ROLLBACK TRAN RAISERROR ('You cannot delete records on Sunday', 16, 1)ENDENDGO |
|
|
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 |
|
|
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 ) |
|
|
|
|
|
|
|