Merge Into inside Trigger

Aug 16, 2011 at 1:48 AM

I'm trying to create a trigger that will run a merge on one table (item_dates) when another table (items) has a row inserted, but it is not working. My SQL statement is below:

create trigger item_dates_insert after insert on items referencing new row as newrow
	for each row 
			merge into item_dates using (newrow.item_id, now, now) AS s(x,y,z) ON s.x = item_dates.item_id
			when matched then update set created = s.y, modifed = s.z
			when not matched then insert (created, modified) values (s.y, s.z)

When I attempt to add the trigger in EffiProz Query Tool I get the below error:

Error -4850 (3F000): invalid schema name: NEWROW 
Failed to execute SQL command.

It looks like the newrow alias is not recognized by the merge statement.  I'm new to EffiProz and its syntax, so I suspect that is the issue here.  Can anyone point me in the right direction?  Any help would be appreciated.

Thanks,

Brian