How do i write an convert an Oracle trigger to Microsoft sql server
CREATE OR REPLACE TRIGGER zip_modify_oracle
AFTER INSERT OR UPDATE
FOR EACH ROW
-- Find ZIP_CODE
IF :new.ZIP_CODE is NULL then
:new.ZIP_YN = 0;
:new.ZIP_YN = 1;
Microsoft SQL sERVER:
CREATE TRIGGER zip_modify_mirosoftsqlserver
Not anywhere close.
That won't even compile. And you don't reference the virtual Inserted table either.
Try this (substitute your actual primary key):
CREATE TRIGGER trOrders_UD
SET ZIP_YN = CASE WHEN ZIP_CODE IS NULL THEN 0 ELSE 1 END
INNER JOIN Inserted on Orders.[PrimaryKey] = Inserted.[PrimaryKey]
Read the section on the virtual Inserted and Deleted tables in Books Online to understand how SQL Server processes triggers.
Last edited by blindman; 07-03-07 at 00:41.
If it's not practically useful, then it's practically useless.
Superdrog - does your job interview test require you convert PL\SQL to T-SQL? The last question could conceivably have been for someone with only a bit of SQL experience - this is definately in a different ballpark.