Help: Creating Trigger

Hi All.
I need to create a trigger in my image table. I want images older than 5 days to be automatically deleted from the table.

example:
create trigger Delete_old_Images

as delete from Image_Table
where ImageID = 5 days old

I do not know how to accomplish this. Can somebody help me.
Thanks in advance

What database are your using.

For Oracle triggering events are those events like INSERT, UPDATE, DELETE, and some system type events. But not on time.

This I fear will need to be done on a schedule unless you want to check for deletes whenever inserts, update, or deletes are done. I would not recomend this as the performance could be quite bad for those types of DML.

If you are using Oracle I would suggest you create a database job or schedule (depending on the version you have) that will check for these deletes.

might be something like this:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'deletem',
job_type => 'PLSQL_BLOCK',
job_action => 'delete from Image_Table where ImageID > sysdate - 5;',
start_date => SYSDATE,
repeat_interval => 'FREQ = DAILY; INTERVAL = 1');
END;

Thanks james Iam not using Oracle I use MSSQL2000

Hi. I tried to run this code in query analyzer.
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'deletem',
job_type => 'PLSQL_BLOCK',
job_action => 'delete from Image_Table where ImageID > sysdate - 5;',
start_date => SYSDATE,
repeat_interval => 'FREQ = DAILY; INTERVAL = 1');
END;

I get syntax error. I am using MSSQL

Thanks in advance

First create a stored procedure that deletes the photos. Then schedule the stored procedure to run once per day?

Hi.
Can you show me the store procedure. I have Problem creating it.

here is my syntax

create proc DeleteImages
as
delete from Product_image where ImageID > sysdate - 5
start_date => SYSDATE
repeat_interval => 'FREQ = Daily Interval = 1'

justnew,
what i gave was for oracle as you didn't give a database initialy. i doubt it will work for MSSQL :)

create proc dbo.DeleteImages
as
delete from Product_image
where DateDiff(dd, NameOfDateField, getdate()) >= 5

Thank you Rawhide. The procedure runs fine all I need to create is schedule the stored procedure to run once per day

Groovy!! And you know how to do that?

Not really I tried to create job schedule I get error. I have not use DTS. Would have been nice if you could show me.
Thanks in advance

What error did you get?


You don't need to use a DTS package to schedule a procedure to run. Whether you put it in a package or not, you do need to have SA rights on the meachine to use SQL Server's scheduler.

If you lack the appropriate permissions to schedule stuff in SQL Server, you can save it as a DTS Package and use your local Windows Task Scheduler to run the package using dtsrun.exe.

It is not a local machine. The SQL Server is a remote server.
Under Enterprise manager. Click on tools -> Job Scheduling -> Transact-Sql Command exec DeleteImages ->on a recuring basis ->Daily ->error 229: Execute permission denied on object sp_help_operator, database 'msdb', owner dbo
Is there any way to program it in Query analyzer.

There are several different ways to schedule it within SQL Server, but they all require SA permissions.

Since you don't have those permissions, follow these steps:

1. Create a new DTS PAckage
2. Add a SQL connection object to the package and set it to your database.
3. Add an Execute SQL Task to your package that executes the procedure: exec DeleteImages
4. Save the package on the SQL Server (remember the name).
5. Open Windows Tash Scheduler.
6. Schedule DTSRun.exe to run the package daily.
7. View the properties of the scheduled task.
8. You will need to manually add the following arguments to the dtsrun execute line:

a. /S: The SQL Server name
b. /N: The name of the DTS Package
c. /E or /U and /P:SQL Server Login credentials (username and password if using a SQL Server account or the trusted connection flag if using your windows account)

The execute line would look something like this if using your Windows account:

"C:\Program Files\Microsoft SQL Server\MSSQL\bin\dtsrun.exe" /S ServerName /N "DTS Package Name" /E

If using a SQL Server account:

"C:\Program Files\Microsoft SQL Server\MSSQL\bin\dtsrun.exe" /S ServerName /N "DTS Package Name" /U MyUserName /P MyPassword


For more info on scheduling DTSRun.exe: http://msdn.microsoft.com=/library/e...tsrun_95kp.asp

For more info on scheduling within SQL Server: http://msdn.microsoft.com/library/en...kgmng_4hm6.asp

Thank you for the info. I will tried it and if any problem I will let you know.