Trigger - SQL Server Forums

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

Trigger

Author

Topic

bibek_rishal
Starting Member

7 Posts

Posted - 2011-01-02 : 11:15:31

Can someone help me to create a trigger which will delete the row after 30 minutes of its insertion. I am new to trigger so please help me with full query..

Kristen
Test

22859 Posts

Posted - 2011-01-02 : 11:56:53

You don't need a trigger (wrong type of animal for this job), you just need to schedule (e.g. using SQL Agent) some SQL to delete "stale" records, and schedule that to run as often as you need (every minute if you need it to delete promptly after 30 minutes, less often if it is not too important if you keep some records a but longer).


DELETE D
FROM MyTable AS D
WHERE MyCreateDateColumn <= DATEADD(Minute, -30, GetDate())

bibek_rishal
Starting Member

7 Posts

Posted - 2011-01-03 : 10:47:35

thanks Kristen for your help....But i could not figure out what does that D means in the query provided...

Kristen
Test

22859 Posts

Posted - 2011-01-03 : 11:28:17

D is an "Alias" for the table you will delete from (named "MyTable" in this example)

bibek_rishal
Starting Member

7 Posts

Posted - 2011-01-04 : 09:53:40

I think this will not solve my problem as i want the row which has been inserted to be deleted automatically after 30 minuted. I am using this query in my php project. I hope u can help me.

Kristen
Test

22859 Posts

Posted - 2011-01-04 : 10:51:29

"I think this will not solve my problem "

Well .. you need to explain why you think this won't solve your problem and we can help you further.

Otherwise we have no clue (as in the absence of a description of a problem I think this will solve your problem)

bibek_rishal
Starting Member

7 Posts

Posted - 2011-01-04 : 11:10:23

I need to create one table which will contain one field, if i insert any data in that field it should be deleted after some time automatically. Do i need to add any other field in my table. I only know about the basic query of sql, so i may have not understood that query which you have added. Should i need to add more field. And should i need to write two query at a time for both insert and delete?

Thank u for giving me time to solve my problem.

Kristen
Test

22859 Posts

Posted - 2011-01-04 : 11:22:54

If you add a "Create Date and Time" column to your table then you can use that to decide which records are older than 30 minutes and can now be deleted (which is basically the query that I have suggested).

There isn't a "trigger" that you can create that will delete a record at some future point in time.

You could perhaps use Service Broker to delete the record "30 minutes in the future" - but I think that would be a "sledgehammer to crack a nut"

Sachin.Nand

2937 Posts

Posted - 2011-01-04 : 11:42:51

Do you know how to create a job in SQL using SQL Agent?

http://msdn.microsoft.com/en-us/library/ms190268.aspx

PBUH

bibek_rishal
Starting Member

7 Posts

Posted - 2011-01-04 : 12:04:05

I have created the following table

Database Name : db_1
Table Name : tbl_1
Table fields :id, username, CreateDateandTime

Can you please right the exact query on the basis of these table detail to solve my problem.Both for insertion and deletion of row.That would be a great help.

And you are right, this task is really being cracking a nut with sledgehammer for me.

Kristen
Test

22859 Posts

Posted - 2011-01-04 : 12:35:30

[code]INSERT INTO tbl_1 (id, username, CreateDateandTime)
SELECT [id] = 123, [username] = 'kristen', [CreateDateandTime] = GetDate()
[/code]
You could set a DEFAULT constraint on [CreateDateandTime] and then you won't have to provide that column in the INSERT

Then you schedule a task:
[code]
DELETE D
FROM db_1.dbo.tbl_1 AS D
WHERE CreateDateandTime <= DATEADD(Minute, -30, GetDate())
[/code]

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-04 : 13:16:15

[SNIP]
i want the row which has been inserted to be deleted automatically after 30 minuted.
[SNIP]
Just to add a bit more in response to Kristen" What i perceived as per SNIP is to automatically remove every inserted row as soon as it becomes 30 min old then perhaps you will need to schedule it for the shortest possible duration ..e.g. it should be executed after a minute. However this would have significant impact on the resource utilization.

Cheers!
MIK

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2011-01-04 : 13:52:50

Bibek, you can't do this with a trigger.
Listen to the alternatives being offered to you.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Kristen
Test

22859 Posts

Posted - 2011-01-04 : 16:57:23

" it should be executed after a minute. However this would have significant impact on the resource utilization."

Actually I don't think that will be a problem.

We have processes that create a log row each time they run. They run often enough to insert 100,000's of row per hour. A process that ones once-a-minute, and just deletes a few rows, is not really asking a lot - it just runs via a schedule, rather than from some user action.

Given that it will only delete, each minute, what some users manages to INSERT within a minute, 30 minutes ago, it should be fine.

bibek_rishal
Starting Member

7 Posts

Posted - 2011-01-05 : 11:07:41

Thank you guys i successfully create the delete query which deletes row which is 30 minutes old but i could not create event for that query to run every 1 minute.

Kristen the query which have written for delete gives this error.

Error
SQL-query :

DELETE D
FROM db_1.dbo.tbl_1 AS D
WHERE CreateDateandTime <= DATEADD(Minute, -30, GetDate())
MySQL said:
You have an error in your SQL syntax near 'D
FROM db_1.dbo.tbl_1 AS D
WHERE CreateDateandTime <= DATEADD(Minute, -30, Get' at line 1

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-05 : 12:27:34

well the query seems to be ok and is running fine with me on SQL 2008

delete d
from dbo.tab1 D
where createdon<=DATEADD(Minute, -30, GetDate())

kindly re-check as if you have executed the complete query instead of executing a portion of it?

@"but i could not create event for that query to run every 1 minute." do you have any idea of the SQL server agent? if you have then create a job for this query as per required time interval..Please note that this option is available in the Standard and enterprise editions and you will not be able to find it in express edition ..

Kristen
Test

22859 Posts

Posted - 2011-01-05 : 12:53:49

"MySQL said: "

Are you running MySQL or Microsoft SQL Server?

If its MySQL then this forum is for Microsoft SQL Server so folk here may not know much/anything about MySQL (I don't!!)

bibek_rishal
Starting Member

7 Posts

Posted - 2011-01-06 : 10:27:38

It doesn't matter now. I have learnt a lot from here. Thank you for your time and co-operation. My problem is solved. And i knew that i have still got much more to study in mysql.

hellohello
Starting Member

12 Posts

Posted - 2011-01-11 : 14:00:31

Can somebody help me with the syntax for writing a trigger each time a row is inserted (e.g a reservation into a reservation table). I need the trigger increment the annual usage column by 1. So I am using an AFTER TRIGGER and im stuck there. :(. If someone could i would appreciate it

hellohello
Starting Member

12 Posts

Posted - 2011-01-11 : 14:47:56

Its ok i got an answer

create trigger amex_trg1 on dbo.reservation_tbl
for insert
as
UPDATE dbo.resource_tbl
SET resource_annual_usage += 1
from resource_tbl
inner join inserted on
resource_tbl.resource_id = inserted.resource_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources