SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need help with the trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zspeed01
Starting Member

6 Posts

Posted - 04/28/2013 :  09:48:32  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 04/28/2013 :  10:35:36  Show Profile  Reply with Quote
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

Edited by - James K on 04/28/2013 10:36:22
Go to Top of Page

zspeed01
Starting Member

6 Posts

Posted - 04/28/2013 :  10:41:11  Show Profile  Reply with Quote
I supposed to use IF EXISTS statement, but this works as well. Thank you very much for your help

Edited by - zspeed01 on 04/28/2013 11:00:58
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 04/28/2013 :  18:11:49  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000