CODEWITHSUNDEEP
sql-servert-sqltriggers
Majid Basirati
Majid Basirati

Reputation: 2875

Insert data into a column of table in SQL Server

I have a table with 2 columns: SolarDate , GregorianDate

And I have a function for convert Gregorian date to solar date in T-SQL. When a row is inserted into this table, it only make fill GregorianDate column.

I want when a row is inserted, SolarDate field is filled too.

Do I need to a trigger? Or another things?

And how can I do this?

Upvotes: 0

Views: 404

Answers (2)

MarkD
MarkD

Reputation: 5316

You can create a computed column on your table that uses your UDF.

CREATE FUNCTION ArbSQ
(
    @Num1 INT
)
RETURNS INT
AS
BEGIN

    RETURN POWER(@Num1, 2)

END
GO

CREATE TABLE MyTable
(
    MyNum INT
    ,MySQ AS dbo.ArbSQ(MyNum) 
)

INSERT INTO MyTable
VALUES (1), (2), (3)

SELECT *
FROM MyTable

In this example (no longer using the scalar UDF), the computed column can be PERSISTED;

CREATE TABLE MyTable
(
    MyNum INT
    ,MySQ AS POWER(MyNum, 2) PERSISTED
)

INSERT INTO MyTable
VALUES (1), (2), (3)

SELECT *
FROM MyTable

Upvotes: 1

marc_s
marc_s

Reputation: 755411

You could use a trigger - or just define your SolarDate as a computed column based on that function that you have.

In that case - you need to first remove that column SolarDate that you already have, and then add it again as a computed column:

ALTER TABLE dbo.YourTable
ADD SolarDate AS dbo.DetermineSolarDate(GregorianDate)

This would constantly and automatically determine the SolarDate whenever you need that value, and you don't need to have a trigger in place for this to work.

Upvotes: 7

Related Questions