Insert with trigger specification, update value

Source: Internet
Author: User
Tags date datetime insert sql server sql mysql

Suppose there is a table A,

CREATE TABLE A (

ID INT not NULL IDENTITY (1,1),

ProductID INT not NULL,

Retailerid INT not NULL,

Date DATETIME not NULL

CONSTRAINT a_pk PRIMARY KEY (ProductID, Retailerid, Date)

);

I mean to make ProductID, Retailerid and Date (excluding hours, minutes, etc.) as primary keys.

That is: one day, Retailerid and ProductID are the only ones.

In SQL Server, DateTime can store hours, minutes, and so on, and if the program is only written by me, I will spec the date to include only the date part. But if you change the database directly, it is only through the trigger to standardize.

Both Oracle and MySQL triggers have two objects: NEW and old, but SQL Server does not, and SQL Server has instered and DELETED logical tables that refer to SQL Server Help.

In Oracle and MySQL, just use:

NEW. FieldName = XXX;

You can change the value of the INSERT or UPDATE, and in SQL Server, for now, I only know that using UPDATE is inconvenient:

CREATE TRIGGER a_t on A for INSERT, UPDATE as

BEGIN

UPDATE A

SET Date = SUBSTRING (CONVERT (VARCHAR, I.date, 20), 1,10)

From INSERTED I

WHERE i.id = a.id

End;

OK, INSERT a try:

INSERT into A (ProductID, Retailerid, Date) VALUES (1, 2, ' 2009-11-16 12:57:43 ');

You can see:

(1 rows affected)

(1 rows affected)

That is to do two operations ...

I don't know if there are any other ways.



Related Article

Alibaba Cloud 10 Year Anniversary

With You, We are Shaping a Digital World, 2009-2019

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.