Insert with trigger specification, update value

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.