Results 1 to 15 of 15

Thread: Help: Creating Trigger

  1. #1
    Join Date
    Apr 2005
    Posts
    8

    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

  2. #2
    Join Date
    Mar 2003
    Posts
    468
    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;
    Last edited by jkoopmann; 04-30-2005 at 11:13 AM.

  3. #3
    Join Date
    Apr 2005
    Posts
    8

    Trigger

    Thanks james Iam not using Oracle I use MSSQL2000

  4. #4
    Join Date
    Apr 2005
    Posts
    8

    Trigger Problem

    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

  5. #5
    Join Date
    Feb 2003
    Posts
    1,048
    First create a stored procedure that deletes the photos. Then schedule the stored procedure to run once per day?

  6. #6
    Join Date
    Apr 2005
    Posts
    8

    Store Procedure

    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'

  7. #7
    Join Date
    Mar 2003
    Posts
    468
    justnew,
    what i gave was for oracle as you didn't give a database initialy. i doubt it will work for MSSQL

  8. #8
    Join Date
    Feb 2003
    Posts
    1,048
    create proc dbo.DeleteImages
    as
    delete from Product_image
    where DateDiff(dd, NameOfDateField, getdate()) >= 5

  9. #9
    Join Date
    Apr 2005
    Posts
    8

    Store Procedure

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

  10. #10
    Join Date
    Feb 2003
    Posts
    1,048
    Groovy!! And you know how to do that?

  11. #11
    Join Date
    Apr 2005
    Posts
    8

    DTS

    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

  12. #12
    Join Date
    Feb 2003
    Posts
    1,048
    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.

  13. #13
    Join Date
    Apr 2005
    Posts
    8
    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.

  14. #14
    Join Date
    Feb 2003
    Posts
    1,048
    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

  15. #15
    Join Date
    Apr 2005
    Posts
    8
    Thank you for the info. I will tried it and if any problem I will let you know.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •