CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008triggers
user760379
user760379

Reputation: 1

how to write trigger?

I am developing an online videos web application. I want to restrict accessing videos in a particular time. I wrote a trigger but I am encountering problems with incorrect syntax. Please help me.

CREATE TRIGGER trig_Update_Employee ON [CourseTopic]
FOR SELECT AS

BEGIN

  DECLARE @week int, @hour int
      SET @week = DATEPART(dw, GETDATE())
      SET @hour = DATEPART(hour, GETDATE())
  IF @week = 3 OR @hour > 10 AND @hour > 10
  BEGIN
    ROLLBACK tran
    PRINT 'class timing is over you can not watch this video at this time.'
  END 
END

Upvotes: 0

Views: 209

Answers (2)

p.campbell
p.campbell

Reputation: 100607

Suggest moving your business logic to a stored procedure instead.

Something like this to suit your business requirements:

 CREATE PROC GetVideos

 AS
     DECLARE @Now smalldatetime = GETDATE();

     SELECT ID, URL FROM Videos
     WHERE  DATEPART(dw, @Now) != 3 
     AND    DATEPART(hour, @Now) <= 10;

Upvotes: 0

gbn
gbn

Reputation: 432561

You can't have SELECT triggers for SQL Server (looks like that dialect). Triggers fire for logged data changes only (UPDATE, DELETE, INSERT)

You'd achieve this by a view or a stored procedure or some other code/client check.

Upvotes: 3

Related Questions