• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 448
  • Last Modified:

SQL Trigger for a Datetime


I wan't to prgramm a trigger that sets the value in the column to NULL IF the datetime in the record is = 30.12.1899 00:00:00

Can anyone please help me how to do this?
0
mr-kenny
Asked:
mr-kenny
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you mean:

CREATE TRIGGER trg_set_null
  ON yourtable
 FOR INSERT, UPDATE
AS
  UPDATE t
     SET your_date_field = NULL
   FROM yourtable t
   JOIN INSERTED i
       ON i.primary_key_field = t.primary_key_field
     AND i.your_date_field <= convert(datetime, '1900-01-01', 120)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello mr-kenny,

CREATE TRIGGER tr_Tablenmae_INSERT
ON Tablenmae
FOR INSERT, update
AS
Update TableName
SET datecolumn = NULL
FROM TableName
inner join inserted on tableName.PrimaryKey = inserted.primarykey
where inserted.datecolumn = '18991230'
go

Regards,

Aneesh
0
 
mr-kennyAuthor Commented:
what is this join inserted doing?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it's making sure the update only looks at the rows that are actually inserted/updated, instead of checking the whole table
0
 
mr-kennyAuthor Commented:
what is this inserted for? thanks for your help

If a new record is inserted in the Table the datetime in my Column called date should set to NULL if the datetime value was 30.12.1899 00:00:00

0
 
mr-kennyAuthor Commented:
in your scripst i'm not sure which names I have to replace can you please format them clearly?
0
 
mr-kennyAuthor Commented:
it worked thanks you both
0
 
mr-kennyAuthor Commented:
thanks
0
 
HainKurtSr. System AnalystCommented:
check here for triggers: http://technet.microsoft.com/en-us/library/ms189799.aspx
CREATE TRIGGER trg_set_null
    ON yourtable
   FOR INSERT, UPDATE
AS
  UPDATE t
     SET your_date_field = NULL
   FROM yourtable t
   JOIN INSERTED i
     ON i.primary_key_field = t.primary_key_field
    AND i.your_date_field = convert(datetime, '12/30/1899', 101);
GO;

Open in new window

0
 
mr-kennyAuthor Commented:
last question: how do I save the trigger in SQL Management Studio?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you run the (sql) script.
if you  "save" the sql to file, it will NOT be save inside the sql server
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now